May 31, 2011 at 7:46 am
Here is my situation.
We are a company that provides software to the Staffing Industry. One of our applications that queries the database is a Sales Analysis Gross Margin report. Below is a snippet of the SELECT / INSERT Over the years we noticed some degradation of the speed of the query as the database became larger and larger. Usually we the query up to 5000 rows of information. The following steps and resources were implemented:
1.) Set up SQL Database Maintenance Plans at the customer site to REBUILD the indexes
2.) Invoke the SQL Tuning and Optimizing Plans through the SQL Server Profiler and Database Engine Tuning Adviser
Based on those recommendations we applied the following Indexes to our Clients Database (see 2nd snippet). The results were great. Queries that were taking 30 minutes went down to 20-30 seconds.
Here is the problem. For some reason these indexes lose their effectiveness during normal daily operations at our Clients. At some clients we run the Database Maintenance Plans every night. Then 2 to 3 hours into a normal work day the query reverts back to its original 30 minute timing. The only way to correct the problem is run the SQL query stand alone during the day. That solves the problem immediately but it is not a solution. The problem happens on SQL 2005, SQL 2008 and SQL 2008 R2. Servers are top of the line. More and more of these servers are Server 2008 R2 with 16 Gig of RAM. My concern is when customer buys new hardware the performance reverts back to before the upgrade. If anything is should be much faster.
The questions are as follows:
1.) What could be causing the Index degradation so early in the day when the Maintenance Plans were just complete the night before?
2.) Why do we need to run the Maintenance Plans so often. Is this common?
3.) What file checks could we be missing that I can implement to solve this problem?
4.) Are there any settings I can put in SQL and that we have overlooked?
5.) In the Database Maintenance Plans should we select both the REBUILD and REORGANIZATION option or just the REBUILD option? I read that if you select both it is self defeating. Is that correct?
6.) Can something be setup in SQL that can automatically correct the index if it detects a problem with the integrity of that Index instead having to run the script stand alone or wait until the Database Maintenance Plan kicks in at its appropriate time.
Any ideas or suggestions would be greatly appreciated
Thanks
Greg
May 31, 2011 at 8:03 am
Update the stats after 90 minutes during the day.
May 31, 2011 at 8:08 am
greg.wollman (5/31/2011)
1.) What could be causing the Index degradation so early in the day when the Maintenance Plans were just complete the night before?
Incorrect statistics? Sounds like it may be the cause. See if this looks familiar: http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/
5.) In the Database Maintenance Plans should we select both the REBUILD and REORGANIZATION option or just the REBUILD option? I read that if you select both it is self defeating. Is that correct?
Rebuild drops and recreates the index.
Reorganise shuffles the index pages back into order
6.) Can something be setup in SQL that can automatically correct the index if it detects a problem with the integrity of that Index instead having to run the script stand alone or wait until the Database Maintenance Plan kicks in at its appropriate time.
I don't think the problem has anything to do with the integrity of the index. Stats are updated automaticaly, but on large tables the frequency may be too low
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2011 at 8:19 am
See why your query is slow, what it is waiting for. You can find a very nice sp_who2 replacement here:
http://sqlblog.com/files/default.aspx
It is procedure sp_WhoIsActive from Adam Mechanic, and displays wait info.
Very common is CX wait caused by parallel query execution. If that is the case, and if your db is OLTP (it is, as you described it, it is not a warehouse), you should set server parameter max degree of paralelism to 1 to avoid that wait.
Other waits are also possible, like locks from many parallel users.
If it is really a statistics and fragmentation problem, I recommend you excellent script by Ola Hallengren that updates statistics and defragments indexes in one step, skipping indexes that are already ok:
http://ola.hallengren.com/Documentation.html#IndexOptimize
Good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply