June 12, 2011 at 9:22 am
We have one server that experience slow response time in the morning.
I got some monitoring alerts, that says some spid is blocked by the other, and master database is suspened, what can I do about it?
June 12, 2011 at 9:51 am
Blocking is normal. Excessive blocking is not, but you'd need to monitor over time to know if the blocks are held for a length of time.
What you ought to do is trace the activity , look for long running queries and either rewrite them, add indexes, or perform some other tuning effort.
The master database should not be suspended. Could you explain that more? Is that something you see in SSMS?
June 12, 2011 at 10:16 am
Thanks, I setup a job using sp_who2 to catch.
See below, the lines may be now in align, but I cannot find a way to post corretlly
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIO
11SUSPENDEDsa..masterCHECKPOINT11528136547
11SUSPENDEDsa..SharedServices2_DBCHECKPOINT11528136570
11SUSPENDEDsa..masterCHECKPOINT11532836600
11SUSPENDEDsa..masterCHECKPOINT11532836600
11SUSPENDEDsa..masterCHECKPOINT11551536665
2SUSPENDEDsa..(null)LOGWRITER166140
2SUSPENDEDsa..(null)LOGWRITER166171
2SUSPENDEDsa..(null)LOGWRITER166171
2SUSPENDEDsa..(null)LOGWRITER166171
2SUSPENDEDsa..(null)LOGWRITER168359
83SUSPENDED.WSS_Content_CollaborationSELECT76640468108
83SUSPENDED.WSS_Content_CollaborationSELECT78281479179
83SUSPENDED.WSS_Content_CollaborationSELECT96500575951
June 12, 2011 at 11:18 am
You will usually see checkpoints and log writes as suspended, these are run periodically by the system.
Is the blocked/blocking process in the output you've posted? I'm guessing it isn't.
June 12, 2011 at 1:58 pm
This is one of them:
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIO
138SUSPENDEDilsSN2.masterDBCC181317267
149SUSPENDEDsssgswe1138tempdbEXECUTE08042
June 12, 2011 at 2:31 pm
At a guess, it looks like 138 could be doing an index rebuild (dbcc dbreindex).
June 12, 2011 at 2:32 pm
At a guess, it looks like 138 could be doing an index rebuild (dbcc dbreindex). Open activity monitor > right click > details next time it happens. Perhaps your maintenance plan is running long or off schedule?
June 12, 2011 at 2:32 pm
Sorry, sqlservercentral seems to have crashed momentarily, hence the multipost.
June 14, 2011 at 1:26 pm
I found more information about the block issue. I setup a job to run SP_who2 around the morning peak hours, but I found nothing collected for the sp_who2 at that time period until later the blocks are gone.
and other transaction log backup are delayed by that too.
I see there are some sharepoint delete expired session jobs run every minute , that could cause the blocks at peak time.
Is it a job needs to run every minute?
I will check if I disable the sharepoint jobs, will it still have the blocks.
And if I add RAM to the server, will that help too.
Thanks
June 15, 2011 at 8:02 am
1) use sp_whoisactive instead of sp_who2. freebie - learn to use it's goodness
2) sharepoint is HORRIBLE from a database perspective! there are quite a few indexes that need to be added (UNSUPPORTED btw!!) to help it perform acceptably.
3) Best option for you is to get a performance tuning professional to help you out for a bit to both resolve current problems and mentor you on how to better understand what is going on and what to do about it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 15, 2011 at 8:59 am
TheSQLGuru (6/15/2011)
1) use sp_whoisactive instead of sp_who2. freebie - learn to use it's goodness2) sharepoint is HORRIBLE from a database perspective! there are quite a few indexes that need to be added (UNSUPPORTED btw!!) to help it perform acceptably.
Unsupported or impossible?
June 15, 2011 at 11:11 am
Ninja's_RGR'us (6/15/2011)
TheSQLGuru (6/15/2011)
1) use sp_whoisactive instead of sp_who2. freebie - learn to use it's goodness2) sharepoint is HORRIBLE from a database perspective! there are quite a few indexes that need to be added (UNSUPPORTED btw!!) to help it perform acceptably.
Unsupported or impossible?
Unsupported, just like most 3rd party database apps. But assuming you are DBO+ you can create any index you want on a table. But if you need support (or are applying a patch) you need to drop them before proceeding and then recreate when done with either.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 15, 2011 at 11:22 am
TheSQLGuru (6/15/2011)
Ninja's_RGR'us (6/15/2011)
TheSQLGuru (6/15/2011)
1) use sp_whoisactive instead of sp_who2. freebie - learn to use it's goodness2) sharepoint is HORRIBLE from a database perspective! there are quite a few indexes that need to be added (UNSUPPORTED btw!!) to help it perform acceptably.
Unsupported or impossible?
Unsupported, just like most 3rd party database apps. But assuming you are DBO+ you can create any index you want on a table. But if you need support (or are applying a patch) you need to drop them before proceeding and then recreate when done with either.
Do they actually look for those while on the call?
June 15, 2011 at 12:54 pm
Ninja's_RGR'us (6/15/2011)
TheSQLGuru (6/15/2011)
Ninja's_RGR'us (6/15/2011)
TheSQLGuru (6/15/2011)
1) use sp_whoisactive instead of sp_who2. freebie - learn to use it's goodness2) sharepoint is HORRIBLE from a database perspective! there are quite a few indexes that need to be added (UNSUPPORTED btw!!) to help it perform acceptably.
Unsupported or impossible?
Unsupported, just like most 3rd party database apps. But assuming you are DBO+ you can create any index you want on a table. But if you need support (or are applying a patch) you need to drop them before proceeding and then recreate when done with either.
Do they actually look for those while on the call?
I have no idea.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 15, 2011 at 12:59 pm
Then why do I HAVE TO delete them before the call? 😉
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply