| Author |
Topic |
|
im1dermike
Posting Yak Master
222 Posts |
 Posted - 2010-03-03 : 14:57:11
|
| I'm trying to identify the cause of a query on a view being slow. It takes about 5 seconds to get the results. I restored the same database on my local server and I'm able to get the results immediately. The only differences I've found are:My server:Version: 9.00.4262.00 SP3 Developer EditionProcessors: 8Memory: 1790 MBProduction server:Version: 9.00.1399.06 RTM Standard EditionProcessors: 4Memory: 16383 MBIs it just the number of processors? Could it be a network issue (my machine and the production server are on different networks)? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-03 : 15:55:46
|
| reviewed the execution plan yet? rebuilt indexes? |
 |
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2010-03-03 : 15:59:58
|
| I'm not sure what you mean when you say "execution plan". I just created indexes for the tables the view uses today (first time creating indexes). I created them right before I ran the query. The data in the tables isn't changing either. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-03 : 16:02:55
|
| I'm very surprised that you have a production server running the RTM version of SQL Server 2005. There have been thousands of fixes since then. You need to make it a priority to get production upgraded to SP3 plus the latest cumulative update package. 4262 is the minimum build I'd recommend for a production server. There are higher CUs though.We can't determine if it's a hardware issue given the information you've provided. It's not likely CPU related though. To determine if you have a hardware bottleneck, you'd need to verify it with PerfMon.Perhaps you just have a bad execution plan in cache on the production server. Did you try DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS on production? Note that this will have a temporary negative impact to production since all of the plans will be cleared from cache. How often do you run update statistics in production? What's your sampling percentage on it? How often do you rebuild the indexes in production?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2010-03-03 : 16:20:39
|
| Thanks for your reply, Tara. Please forgive me. I'm just a lowly C#/ASP programmer forced to handle the tasks of database administrator/architect in my small, non-profit company. We've recently discussed about hiring a database person for a few weeks to look at our system. I'm sure this is long overdo... I will try to respond as best I can to your posts.- I will have to read up on PerfMon to see about running it.- I just ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS on production and then re-ran my query. It was still slow.- I don't know what update statistics are, nor sampling percentage. Up until the other day, I didn't know what indexes are and there were none in place. This morning I created some simple indexes on a handful of tables. I believe it sped up the query a little, but it's still stuck at 5 seconds which isn't really acceptable.- I don't know what max degree of parallelism is either, but when I run sp_configure, I get the following (on both servers):allow updates 0 1 0 0clr enabled 0 1 0 0cross db ownership chaining 0 1 0 0default language 0 9999 0 0max text repl size (B) 0 2147483647 65536 65536nested triggers 0 1 1 1remote access 0 1 1 1remote admin connections 0 1 0 0remote login timeout (s) 0 2147483647 20 20remote proc trans 0 1 0 0remote query timeout (s) 0 2147483647 600 600server trigger recursion 0 1 1 1show advanced options 0 1 0 0user options 0 32767 0 0 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-03 : 16:23:02
|
| Yes it will still be slow after you run the DBCC commands as the cache is now clear. Did you run it twice though? The first time you run it after the cache is cleared, it will be slower than normal. It should speed up after subsequent tries.You'll need to enable "show advanced options" in order to see the setting.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2010-03-03 : 16:29:44
|
| The second and subsequent query runs appear to take 4 seconds.Ad Hoc Distributed Queries 0 1 0 0affinity I/O mask -2147483648 2147483647 0 0affinity mask -2147483648 2147483647 0 0Agent XPs 0 1 0 0allow updates 0 1 0 0awe enabled 0 1 0 0blocked process threshold 0 86400 0 0c2 audit mode 0 1 0 0clr enabled 0 1 0 0cost threshold for parallelism 0 32767 5 5cross db ownership chaining 0 1 0 0cursor threshold -1 2147483647 -1 -1Database Mail XPs 0 1 0 0default full-text language 0 2147483647 1033 1033default language 0 9999 0 0default trace enabled 0 1 1 1disallow results from triggers 0 1 0 0fill factor (%) 0 100 0 0ft crawl bandwidth (max) 0 32767 100 100ft crawl bandwidth (min) 0 32767 0 0ft notify bandwidth (max) 0 32767 100 100ft notify bandwidth (min) 0 32767 0 0index create memory (KB) 704 2147483647 0 0in-doubt xact resolution 0 2 0 0lightweight pooling 0 1 0 0locks 5000 2147483647 0 0max degree of parallelism 0 64 0 0max full-text crawl range 0 256 4 4max server memory (MB) 16 2147483647 2147483647 2147483647max text repl size (B) 0 2147483647 65536 65536max worker threads 128 32767 0 0media retention 0 365 0 0min memory per query (KB) 512 2147483647 1024 1024min server memory (MB) 0 2147483647 0 0nested triggers 0 1 1 1network packet size (B) 512 32767 4096 4096Ole Automation Procedures 0 1 0 0open objects 0 2147483647 0 0PH timeout (s) 1 3600 60 60precompute rank 0 1 0 0priority boost 0 1 0 0query governor cost limit 0 2147483647 0 0query wait (s) -1 2147483647 -1 -1recovery interval (min) 0 32767 0 0remote access 0 1 1 1remote admin connections 0 1 0 0remote login timeout (s) 0 2147483647 20 20remote proc trans 0 1 0 0remote query timeout (s) 0 2147483647 600 600Replication XPs 0 1 0 0scan for startup procs 0 1 0 0server trigger recursion 0 1 1 1set working set size 0 1 0 0show advanced options 0 1 1 1SMO and DMO XPs 0 1 1 1SQL Mail XPs 0 1 0 0transform noise words 0 1 0 0two digit year cutoff 1753 9999 2049 2049user connections 0 32767 0 0user options 0 32767 0 0Web Assistant Procedures 0 1 0 0xp_cmdshell 0 1 0 0 |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2010-03-03 : 16:31:35
|
| How do I use Perfmon? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2010-03-04 : 09:04:31
|
| I will look into Permon. In the mean time, I decided to open up SQL Server Profiler and compare the query runs on each server. There was a drastic difference in the values for Reads and CPU. On my (fast) server), the values for CPU and Reads are 422 and 30,215 respectively. On the production (slow) server, the values for CPU and Reads are 5,734 and 432,699 respectively. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-04 : 10:01:51
|
| rebuild all the indexes. make sure you aren'tmissing any indexes |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
Posted - 2010-03-04 : 10:27:38
|
| They (only 4 indexes) are all identical and I just rebuilt them all. No change. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-04 : 11:00:52
|
| it's either indexes or data. as i said earlier, what does the execution plan show you? there's a reason it's performing more 14x the reads...either because it's scanning, or because there's 14x as much data.the cpu time is simply a function of performing all those reads and is not significant here |
|
|
|
im1dermike
Posting Yak Master
222 Posts |
|
|
|
Slow query - # of processors?