June 28, 2011 at 9:31 am
Hi:
We have a piece of third-party accounting software that uses SQL Server 2005 as its back end. We've discovered that when a year is balanced and closed out (meaning that a very large amount of data is changed in a short amount of time), many of the general ledger reports and balance sheets start running very slowly.
At the vendor's suggestion, we have a monthly job that performs the following steps:
1. Rebuild Indexes
2. Update Statistics
3. Shrink Database
4. Check Integrity
I checked the stored procedures behind steps 1 and 2, and they appear to run only on tables. I'm wondering if maybe the reports are using indexed views, and that's what's leading to the performance issues after a large amount of data is changed. Am I totally off in this line of thinking? And if this is what's contributing to the performance issues, would it be a better idea to rebuild the views' indexes or rebuild the views themselves?
Thanks!
June 28, 2011 at 9:35 am
you really shouldn't be shrinking your database on a regular basis, this is bad advice from your vendor and may cause the perfromance issue.
also i would do the integrity check more regulary (like every week) as once a month is a long way to have to go through your backups if you find an issue
June 28, 2011 at 9:35 am
here's my quick take.
1. Rebuild Indexes - good.
2. Update Statistics - only for those columns NOT participating in an index, since the Rebuild of the Index takes care of the stats for those columns
3. Shrink Database -NEVER NEVER NEVER shrink a production database; bad advice
4. Check Integrity - always a good idea, wouldn't affect performance though.
i'd bet that #2 is the main culprit; your closeout process probably inserts and updates a lot of data during the closeout process...statistics could easily go stale. I'd look here first.
Lowell
June 28, 2011 at 9:57 am
Thanks to you both for responding! Could either of you explain to me what negative effect shrinking the database would have on performance? I want to be able to explain it to the vendor so they can explain to me why they think it's necessary.
As far as the performance issue goes, I'm not sure what my next step should be. Do you think I should add the views to the Update Statistics step?
June 28, 2011 at 10:26 am
If the Indexes are heavily fragmented you will achieve better results by rebuilding the index as opposed to Reorganizing the index.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2011 at 10:31 am
You read my mind! I just ran a query on the views' indexes, and here's the fragmentation I came up with:
94.2108174245345
97.9705280822277
97.8662604798711
99.1450259688374
99.1922024167167
I don't know much about fragmentation, but I'm going to go ahead and guess that that's not good. 🙂
June 28, 2011 at 10:37 am
lk-681111 (6/28/2011)
Thanks to you both for responding! Could either of you explain to me what negative effect shrinking the database would have on performance? I want to be able to explain it to the vendor so they can explain to me why they think it's necessary.As far as the performance issue goes, I'm not sure what my next step should be. Do you think I should add the views to the Update Statistics step?
This pretty much says it all:
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 10:55 am
Thank you for the link! This whole thing makes a lot more sense now. So basically, shrinking the database is what's fragmenting the indexes, and the Rebuild Indexes step is only rebuilding the tables' indexes, so the views' indexes just get more and more fragmented every time the maintenance process runs.
June 28, 2011 at 11:01 am
lk-681111 (6/28/2011)
Thank you for the link! This whole thing makes a lot more sense now. So basically, shrinking the database is what's fragmenting the indexes, and the Rebuild Indexes step is only rebuilding the tables' indexes, so the views' indexes just get more and more fragmented every time the maintenance process runs.
How do you know for sure it's only working on the tables? Can you post the script so we can have a look?
June 28, 2011 at 11:11 am
Well...I hesitate to put the whole proc here since it's the vendor's code and I don't want to get in trouble, but here's the part that tells me it's only applied to the tables:
SELECT SO.[NAME] TBLNAME,
'01/01/1900 12:00:00AM' INDEXEDDTTM, SI.[NAME] INDEXNAME,
CASE WHEN SI.INDID = 1 THEN 1 ELSE 0 END CLUSTEREDYN
INTO #INDEXEDTABLES
FROM SYSINDEXES SI
LEFT JOIN SYSOBJECTS SO ON SI.[ID] = SO.[ID]
WHERE XTYPE = 'U' AND SI.INDID <> 0 AND SI.INDID <> 255
AND UID = 1
AND INDEXPROPERTY(SI.ID, SI.NAME, 'ISSTATISTICS') = 0
GROUP BY SO.[NAME], SI.[NAME], SI.INDID
The proc loops through #INDEXEDTABLES to rebuild the indexes.
June 28, 2011 at 11:16 am
I've never tried this (never used indexed views in prod) but you could try this simple tweak and see if it runs :
WHERE XTYPE = 'V'
If that works and just change to IN ('U', 'V')
Make sure you try this out on a test server first.
June 28, 2011 at 11:20 am
Also what does the UID filters?? It seems to filter out only on dbo owned objects. I would possibly whack this if you have objects not owned by that schema.
Alternatively you could use a real script to reindex on what's required and save a crapload of time (technical term).
June 28, 2011 at 11:54 am
Finally, a technical term with which I'm familiar! 🙂
Apparently the UID filter is by design; the vendor's notes in the code "explain" why:
--Only do tables owned by dbo. Otherwise, DBCC DBREINDEX below fails
I'm actually not sure that I'm allowed to change any of the code without voiding our warranty. I think I might be stuck just sneaking my own separate job in to rebuild the views' indexes. It's that or contact the vendor, wait six months for action to be taken, and pay them a fee to add a couple characters to the existing proc.
June 28, 2011 at 2:01 pm
lk-681111 (6/28/2011)
Finally, a technical term with which I'm familiar! 🙂Apparently the UID filter is by design; the vendor's notes in the code "explain" why:
--Only do tables owned by dbo. Otherwise, DBCC DBREINDEX below failsI'm actually not sure that I'm allowed to change any of the code without voiding our warranty. I think I might be stuck just sneaking my own separate job in to rebuild the views' indexes. It's that or contact the vendor, wait six months for action to be taken, and pay them a fee to add a couple characters to the existing proc.
I understand the feeling. However I don't see any reason not to take a backup, restore it to a test server. Change the sp to see if you can get it to work with views too.
As for the DBO warning my bet is that they are hardcoding the schema in there so the dynamic sql will fail because it tries to work on a non-existing object.
Anyhow, cost you nothing and shouldn't break the SLA if you make your own proc to maintain the indexes... and the one I sent you is in production in 1000s of servers (low end guesstimate) so I wouldn't worry too much about putting it to work ;-). You need to test in a big enough window for sure but that's the only thing!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply