October 19, 2011 at 12:11 pm
Quest Performance Analysis says this is taking up 90% of our production resources right now. Our system is getting hammered with timeouts because of this. What is this? ANY help is appreciated. I've been googling all morning and the only thing I've found is some handbagfanatic site that McAfee blocks because it's not safe...
October 19, 2011 at 12:12 pm
Running the SQL Profiler GUI perhaps?
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
October 19, 2011 at 12:14 pm
I'll check, good thought. Only myself, and our DBA have the permissions to run that though....we'll see.
October 19, 2011 at 12:27 pm
Damn, says it's coming from our application....just started happening yesterday and we haven't done a code move in a month. :angry:
October 19, 2011 at 12:29 pm
The "heavy hitters" in Quest are all Create Table #tempTable statements inside stored procedures. Any idea why a simple create temp table statement could be the cause of this? (If that truly is the cause)
October 19, 2011 at 12:36 pm
No. SQLTrace is profiler-related. Are you running the profiler GUI? (regardless of where it's coming from)
Hate to say this, but shut down the monitoring tool and see if it's still a factor.
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
October 19, 2011 at 12:53 pm
Nobody running SQL Server Profiler. DBA hasn't responded yet but she usually runs PSSDiag if she does any tracing...and she usually tells me if she's going to...
I'd have to ask her to shut down the Quest tools, but then we'd have to rely on the users to tell us how performance is.....and they always say it's "slow" on a good day :crazy:
October 19, 2011 at 1:02 pm
gregory.anderson (10/19/2011)
I'd have to ask her to shut down the Quest tools, but then we'd have to rely on the users to tell us how performance is.....
No, you can query the wait stats directly from the server. In fact, that's probably a good thing to do now so you have something to compare against once the monitoring tool is off.
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
October 19, 2011 at 1:26 pm
GilaMonster (10/19/2011)
gregory.anderson (10/19/2011)
I'd have to ask her to shut down the Quest tools, but then we'd have to rely on the users to tell us how performance is.....No, you can query the wait stats directly from the server. In fact, that's probably a good thing to do now so you have something to compare against once the monitoring tool is off.
Is that via spwho procs?
DBA is running ProcessExplorer and PerfMon, would those do it?
October 19, 2011 at 2:12 pm
gregory.anderson (10/19/2011)
Is that via spwho procs?
I suppose you could, but it's far from the best way. Read up on wait stats and monitoring wait state
DBA is running ProcessExplorer and PerfMon, would those do it?
Neither of those is Profiler.
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
October 19, 2011 at 2:28 pm
You might be running the trace on the already busy server due to which the trace is contributing in locking the queries that are running on the server.
October 21, 2011 at 9:20 am
Update for this problem (fixed).
Our DBA rebooted our SQL Server, and that didn't fix the problem. Our backups job started later on that night and got hung up somehow. Had to reboot again. During the second reboot, she found the cause/problem.
Our SQL Servers are in a Polyserve matrix environment. One of the sql servers in that matrix was removed to do an upgrade. While doing that, the Polyserve system didn't know that that server was offline/not available. While it was offline, and during business hours, all of our traffic to our regular production sql server was going, and while the database was processing the queries, it was also trying to "find" that other sql server that was offline. This apparently was causing that SQLTRACE_LOCK.
Once we told Polyserve that that server was not available, everything went back to normal.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply