March 6, 2014 at 2:51 pm
Hi there,
My test and live server are alike in terms of configuration(CPU,memory etc).Databases are just a month old on test compared to live and live has only 10% more data .
A particular procedure takes 10 minutes to complete on test but the same takes around three hours on live so I can’t come to conclusion what’s causing it to overrun?
thanks
March 6, 2014 at 3:02 pm
Check the following things in production:
1. Blockings.
2. Resources (CPU, memory) consumption by other processes.
3. Index fragmentation.
4. Whether statistics is fresh.
5. File-system I/O througput
March 6, 2014 at 3:20 pm
Do you do Index Rebuilds and Update Stats in Production ?
--
SQLBuddy
March 6, 2014 at 4:28 pm
It's hard to know for sure, but on a guess, statistics. I'll bet they're not being well maintained on either machine. Minimum, start with sp_updatestats. After that, you may find that some statistics need more frequent updating or need to be updated using UPDATE STATISTICS WITH FULL SCAN.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2014 at 11:55 pm
thanks I updated stats using sp_updatestats and I also used sp_msforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN' but i can still see the fragmenation as below
IndexTypeavg_fragmentation_in_percent
CLUSTERED INDEX 97.6744186
CLUSTERED INDEX 97.43589744
CLUSTERED INDEX 97.14285714
NONCLUSTERED INDEX97.02970297
NONCLUSTERED INDEX96.77419355
NONCLUSTERED INDEX96.66666667
HEAP 94.44444444
NONCLUSTERED INDEX93.5483871
NONCLUSTERED INDEX92
NONCLUSTERED INDEX91.11111111
NONCLUSTERED INDEX90.625
March 7, 2014 at 12:57 am
is upadating stats every 15 minutes is a bad practice? I have decided to do that because it has improved perfromance
March 7, 2014 at 1:39 am
Sqlsavy (3/6/2014)
thanks I updated stats using sp_updatestats and I also used sp_msforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN' but i can still see the fragmenation as below
Update Stats doesn't affect fragmentation.
is upadating stats every 15 minutes is a bad practice?
In most cases that's probably overkill. If you're updating all the stats on every table, then yes it's probably a bad idea. Targeted stats updates, just to the specific stats/table which needs frequent updates after you've identified that frequent updates are needed is fine.
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
March 7, 2014 at 3:13 am
Thanks. Does updating stats on heap table is of any use?
How to reduce fragmentation <30?
IndexTypeavg_fragmentation_in_percent
CLUSTERED INDEX 97.67442
CLUSTERED INDEX 97.4359
CLUSTERED INDEX 97.14286
NONCLUSTERED INDEX97.0297
NONCLUSTERED INDEX96.77419
NONCLUSTERED INDEX96.66667
HEAP 94.44444
NONCLUSTERED INDEX93.54839
NONCLUSTERED INDEX92
NONCLUSTERED INDEX91.11111
NONCLUSTERED INDEX90.625
March 7, 2014 at 3:24 am
Sqlsavy (3/7/2014)
Thanks. Does updating stats on heap table is of any use?
Yes
How to reduce fragmentation <30?
IndexTypeavg_fragmentation_in_percent
CLUSTERED INDEX 97.67442
CLUSTERED INDEX 97.4359
CLUSTERED INDEX 97.14286
NONCLUSTERED INDEX97.0297
NONCLUSTERED INDEX96.77419
NONCLUSTERED INDEX96.66667
HEAP 94.44444
NONCLUSTERED INDEX93.54839
NONCLUSTERED INDEX92
NONCLUSTERED INDEX91.11111
NONCLUSTERED INDEX90.625
Rebuild indexes
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
March 7, 2014 at 3:27 am
Sqlsavy (3/7/2014)
Thanks. Does updating stats on heap table is of any use?How to reduce fragmentation <30?
IndexTypeavg_fragmentation_in_percent
CLUSTERED INDEX 97.67442
CLUSTERED INDEX 97.4359
CLUSTERED INDEX 97.14286
NONCLUSTERED INDEX97.0297
NONCLUSTERED INDEX96.77419
NONCLUSTERED INDEX96.66667
HEAP 94.44444
NONCLUSTERED INDEX93.54839
NONCLUSTERED INDEX92
NONCLUSTERED INDEX91.11111
NONCLUSTERED INDEX90.625
Although, it does depend on the size of the index. If those indexes are small, less than 8 pages in size, they'll never defrag. If they're less than 100 pages in size, I wouldn't bother.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2014 at 4:23 am
Thank you all.
How frequently Index rebuild job can be run? every night or weekly?
March 9, 2014 at 4:34 am
Sqlsavy (3/9/2014)
Thank you all.How frequently Index rebuild job can be run? every night or weekly?
It depends on what you can afford on your system and, how much the indexes fragment. Most systems aren't going to get indexes that are very fragmented in a 24 hour period. But, if yours are, you may want to run it more frequently.
I'd suggest you use one of the scripts that's available online from people who really know this stuff. The one I prefer is by Michelle Ufford[/url]. Someone is sure to suggest Ola Hollengern's too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2014 at 3:55 am
Thanks. I have managed to rebuild indexes and also introducing new indexes helped immensely.
Issue now is with Microsoft ASPSTATE database ‘UPDATE [ASPState].dbo.ASPStateTempSessions’ is blocking every other user process on the database server, blocks are temporary they don’t stay long though and delete expired session job runs every 5 minutes on ASPSTATE.
Any thoughts?
Thanks
March 13, 2014 at 5:20 am
ASPState is difficult to deal with. Generally, isolating it as much as possible is the best bet. Better bet, don't use it for state management of your ASP pages. Storing that stuff to SQL Server is not terribly efficient.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2014 at 5:34 am
Thanks Grant.
Transaction logs are growing rapidly during index rebuild transaction log backup is scheduled every 15 minutes still logs are getting big and I have to shrink them all the time.
I am bit concerned about scheduling index rebuild job as I may end up with log drive full issue.
Before index rebuild I set database recovery model to Bulk logged even then logs are growing rapidly
Could you please advise?
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply