February 1, 2013 at 7:27 am
Hi All,
hoping someone can maybe help shed some light here. I'm running a DTA against one of my databases but while its running I'm constantly picking up alerts against numerous different PIDs.
I've had a look at the logs and all the stack dumps are producing the same outcome.
02/01/2013 13:55:18,spid117,Unknown,* exec dbo.proc_MSS_CommitTransactions 1<c/>1<c/>1<c/>253<c/>0x00003C110000
02/01/2013 13:55:18,spid117,Unknown,* Input Buffer 510 bytes -
02/01/2013 13:55:18,spid117,Unknown,* Access Violation occurred reading address 0000000000000000
02/01/2013 13:55:18,spid117,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
02/01/2013 13:55:18,spid117,Unknown,* Exception Address = 0000000001004E46 Module(sqlservr+0000000000714E46)
02/01/2013 13:55:18,spid117,Unknown,*
this is occurring only when running the DTA (which fails). To try and pin point i ran another very small trace and then began the DTA again and again once the workload is consumed and it begins to perform the analysis, the events return. Is there something I'm missing when running the DTA or can someone point me to a nice tutorial on running the Tuning advisor?
the env is SQL 2008 R2 on SP2 (10.50..4000), Server 2008 R2. I'm quite new to SQL troubleshooting so please let me know if there's anything else i can provide to help
thanks
February 1, 2013 at 7:39 am
I also want to add they all seem to point to the "CommitTransactions" sp within that db i was analysing. the DB itself is part of the MS FAST search for SharePoint configuration and the SP in question as far as i can see and am aware has not been altered
February 1, 2013 at 8:17 am
Honestly, the best advice I can give you on running the tuning advisor is: Don't.
But, it sounds like you're capturing the load, but that you're not restoring the database to the point in time at which the load was captured. In order to get a successful play back of a trace you need to reset the database so that all the data looks exactly as it did during the transactions in question.
"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
February 1, 2013 at 8:27 am
thanks for the advice, I've been trying to get our customer to invest in the SQL Monitor tool instead of me having to constantly review every single thing they have but thats quite difficult as they have a 3rd party team constantly making dev changes to SharePoint so I've no idea what custom queries are in there as well
what kind of recommendations would you have to try and improve performance? I'm a little concerned that the DB I'm looking at has no PK's at all yet has a clustered index assigned. Forgive me for my noobyness but this seems strange to me :ermm:
February 1, 2013 at 8:36 am
Clustered indexes is slightly more important than primary keys, but yeah, I'd want both. You're right there.
But, you said this was a Sharepoint database? You can't really modify the structure of it at all. Microsoft will come after you (not really, you'll just break Sharepoint). What you can do is add indexes, but they'll be subject to getting dropped when updates or patches are applied.
Since you're posting in the 2008 forum I'll assume that's what you're working with. I'd use sys.dm_exec_query_stats to identify the most frequently called and longest running queries that are in cache and concentrate on tuning them. That's a better approach than just responding when someone tells you something is slow. You can combine that dynamic management object (DMO) with sys.dm_exec_query_plan to see the execution plan and understand where indexes can be applied in that manner. This approach is slower than using the DTA, but it's also going to be more accurate.
"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
February 1, 2013 at 9:22 am
thanks again for the info.
the instance I'm working on is entirely for SharePoint, the database I'm reviewing though is part of the FAST Search for SharePoint application in case that helps?
I'll take a look on MS forums and see if there's any reason these database's were created without PK's (after reviewing, none of the FAST Search DB's have PK's) as it had me really confused on the off chance this is by design. I ran a query against the dm_exec_query_stats and for top 25 and found a good bit of info form that, thanks for the help 🙂
February 1, 2013 at 9:48 am
I'm not familiar with that particular part of Sharepoint. Sorry. It does sound like you're largely on the right track.
"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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply