question

Mukesh_Kumar avatar image
Mukesh_Kumar asked

Memory Utilization

Dear Friends, My SQL DB is installed on Win Server 2008 Ent and SQL DB is SQL Server 2008 R2 64 Bit Std. Edt. This database is accessed by application which is design and develop on two different platform, on is develop in C# with .netFramwork 3.0 and another is develop on Visual Foxpro. Both are desktop application and client application is installed on every user’s computer. When I watch activity monitor of SQL Server, I notice memory utilization is very high. A long list of host name they are using application dev in visual foxpro. Client’s machine name is display as host name and every instants using 16 KB. Another interested thing when I capture some trace and found same procedure take different time to start and execute in application. For example ApplicationName LoginName CPU Reads Writes Duration StartTime EndTime ExecutionTime Microsoft Visual FoxPro TASS 156 4479 0 209801 31:47.1 31:47.3 00:00:00:210 .Net SqlClient Data Provider TAKO\tms 31 226 0 110563 31:49.1 31:49.2 00:00:00:110 Is any suggestion for optimize this ? Thanks & Regards Mukesh Kumar
memory-utilization
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
SQL Server will use all the memory you give it, so that's completely normal. The differences in execution times could be from multiple reasons. You may just be seeing differences based on contention of resources and blocking. You might be seeing differences caused by different execution plans due to differences in connection settings. First, check your wait statistics (sys.dm_os_wait_stats) to determine where you may be experiencing bottlenecks on the system. You can also check these multiple times during the day in order to see differences, or before and after you run a query to get an idea how that query behaves. That will tell you more if you have memory issues or not. Second, capture multiple executions of the query from both FoxPro & C#. Average them out to be sure you're not just seeing a one-off event. Also capture the execution plans for each and compare them to see if you're getting different plans. After you have this information, then you can determine if there are things you need to do to modify the query, indexes, table structures, memory settings or something else on the server in order to make things run faster.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.