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
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.