November 14, 2007 at 10:25 am
We've recently upgraded to SQL Server Enterprise 64-bit. Since the upgrade, reporting querries are extremely slow. We found one issue related to the spids going into suspended state but after setting the max degree of parallelism to 1, this stopped. However, query performace has still not improved.
We're not seeing issues on the transactional side but reporting is. Typical stored procedure here are dynamic and returning 500-1000 rows. Smaller procedure are still running fine though.
Anyone have any idea on what to look for on this?
Thanks,
Dave
November 14, 2007 at 10:53 am
start with the execution plan
November 14, 2007 at 1:06 pm
Make sure you update your stats with "FULLSCAN" and that you rebuild your indexes.
After that the runaway plans can be revisited I have found only 2 cases in which the queries had to be re-written.
Cheers,
* Noel
November 14, 2007 at 1:10 pm
We tried updating the stats already and it didn't have any effect. What I have found is that all of the problem procedures use scalar functions for a portion of their result set. After these are commented out, the query runs as expected. This wasn't an issue in 2000 so I'm wondering what's different in 2005?
November 14, 2007 at 1:28 pm
our reporting people are notorious for creating tables with no PK's and other weird things and having queries that do 500000 reads to return 2 rows or go crazy with temp tables
only instance here where a 64 bit server with 20GB RAM and less than 10 users is at 100% CPU and people complain about performance while other 64 bit servers with hundreds of users or queries that process millions of rows barely break a sweat
i would start with a profiler session and see where the hold up is. i also like to look at the reads compared to rows returned. my ideal is 2 to 1 ratio, but 10 to 1 is OK in a lot of instances especially with joins.
bad queries or db design can result in a lot of I/O. i would also look at your disk design. if you can set up RAID 1 +0 or whatever it's called where it's striped RAID1 in one logical volume
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply