July 2, 2010 at 10:48 am
We have a SQL server dedicated to one application. This application creates 2 databases for each new case that is created within it. Most of these databases are very small, most are not even over 100MB very few go over 1GB. Currently there's about 100 small DBs on this instance.
The SQL server is a clustered (2 nodes) dual quad core, 32GB box running Server 2003 R2 and SQL 2005 SP3, 64bit versions for both Os and SQL.
Ever since this application was implemented, there have been performance 'issues'. Most, if not all of them, have been traced back to the application itself and not SQL. However, every now and then there's some inexplicable hiccup in performance.
The vendor, as any other vendor does, was initially quick to point to SQL as the performance culprit and suggested a maintenance plan that the people in charge of this application quickly implemented without giving any though to it. I'm taking about blanket index reorgs/rebuilds, update stats jobs that were happening BEFORE these index reorgs/rebuilds, and weekly database shrink jobs (ugh, I know).
Since taking over this SQL server administrative wise (more like thrown into actually) a couple of weeks ago, I disabled these jobs and implemented Ola Hallengren’s maintenance scripts. Though these have help maintain the overall health of the SQL server, the users of this application are still complaining of poor performance.
I'm pretty confident SQL is not the culprit but I'm in need of help to make my case. Today, one of the leads from the application team raised the concern that the index reorg/rebuild might not be working correctly. He provided an example of an index that has not been optimized since I implemented the new maintenance scripts. The example he provided was of an index that had a page count of 6 (Yeah, I know). The parameters I setup on the Olla's scripts will ignore indexes with less than 1000 pages, which is why this index has not been touched. Right now I'm in the process of replying to his concerns and explain why indexes such as these wont benefit from being reorganized/rebuild.
My position is that the vast majority of the indexes are so small as to rule out their fragmentation level as the issue. One of the things I want to show him is the page count for ALL the indexes on the server. I need help querying this info.
July 2, 2010 at 10:51 am
Query sys.dm_db_index_physical_stats. You'll need to use the detailed mode, and filter for index_level=0
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
July 2, 2010 at 10:57 am
Jose A. Hernandez (7/2/2010)
Since taking over this SQL server administrative wise (more like thrown into actually) a couple of weeks ago, I disabled these jobs and implemented Ola Hallengren’s maintenance scripts. Though these have help maintain the overall health of the SQL server, the users of this application are still complaining of poor performance.
Gut feel, poor indexing, badly written queries. Can you get an independent consultant in to check things over?
May help you...
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
July 2, 2010 at 12:16 pm
It's definitely an application issue, of that I have no doubt. Our Apps team has already identified several instances of bad queries since the application was deployed. We also brought in a consultant shortly after I was thrown in the DBA role and implemented some best practices around the SQL server configuration (multiple temp db files, memory tuning, etc.) We monitor the SQL server via SCOM and the thing is not breaking a sweat by any means.
But questions like these keep popping up and I'm trying to be proactive.
July 7, 2010 at 8:27 am
i would start a profiler trace and capture the 'Tuning' template. dump it into a database and sort by 'Duration'
the text data for the highest duration numbers can be sent back to the devs for them to fix.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply