May 17, 2004 at 6:18 am
We have a database to which we add approximately 1,000 rows per month to the primary table. There is a large number of secondary (and tertiary) tables with all the PK-FK relationships defined, triggers to update the secondary, etc tables based on insert/update/delete on the primary table. The db is accessed via a Java app running on a Weblogic app server.
The problem seems to be a locking issue. This started when we moved the database from a much slower, older server to a brand new "high performance, high availability" clustered server. I have to qualify this in that our corporate SQL support team is really the Intel Server team. They are not SQL server configuration experts by any stretch of the imagination.
Anyway, we're seeing this problem on an almost daily basis. Through EM we can see lots of locks waiting on tables, but nothing is listed as "blocked" or "blocking". The entire database slows to a crawl, even access through EM, except to look at the management data.
How do we determine what is going on here? We have extensive debugging info in the Java side, but all it's telling us is that programs are failing due to no response from SQL. What can we do in SQL to find out who the culprit is? I assume there are Trace flags that can be set to follow the goings on, but I don't know what they are, how to set them or how to interpret them once I have the data.
I know we should have someone go to a Tuning class, but that's the SQL team and they're in a different division so my ability to force that issue is limited.
Any help would be greatly appreciated.
If you need more information please let me know.
May 18, 2004 at 1:03 am
According to the symptoms, I would do some or all of the following:
1. Reduce the number of triggers and replace them with stored procedures. I don't know the exact application workflow and how much of the SQL/s.p. calls is sent directly from the Java app , but I don't like cascade updates.
2. Check the behavior of Java when working with MS SQL Server databases (locking options/flags etc.).
3. Observe the "SQLServer:Access Methods - Table Lock Escalations/sec" performance counter with perfmon. Here is a usefull link:
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
4. Do intensive research on the locking hints and lock escalation issues. Here are some links that I found on SQL Server Central:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=112274
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=109759
I hope this helps.
Regards,
Goce Smilevski.
May 18, 2004 at 6:26 am
What about indexing? Did you remember to move your indexes as you moved to the New server?
Regards, Hanslindgren
May 18, 2004 at 6:27 am
Turn on SQL Profiler, capturing the output to a table (this also has a cost, unless you're writing to another server). Make sure that you're getting the SQL/stored procedure calls, and the duration of the statements.
Then, query that table to find out which SQL statements / sprocs have the highest duration...and there you will likely find your culprit.
P
May 18, 2004 at 6:40 am
Yes, we moved the indexes. The database was moved using backup/restore. All the indexes, etc are in place. I did notice a fair amount of fragmentation so I'm thinking I'll defrag the highly fragmented indexes and update stats on all of them.
I tried running Profiler but it was collecting data on all sorts of unrelated server activity. I put in filters to limit it to the specific database, but that didn't seem to help. Not having much (read "any") experience with Profiler I'm not sure how to get it running effectively for this purpose and how to interpret the results. We have nobody here who can do this (see issues in first post) so I'm kind of stuck unless there's a simple way to do this.
I did turn on Trace Flag 1204 so that locking problems will be reported to the error log, but that may be too late to diagnose the problem.
May 18, 2004 at 6:53 am
OK. I usually monitor Stored Procedures - RPC Completed and TSQL - SQL Batch completed. In the Data Columns tab, defaults are usually fine. Makre sure TextData and Duration are there.
Then, let it run for...1 hr? 2 hrs? Long enough that a representative sample of the queries run on this server are run.
Then, turn off the profiler.
Then, try something like
SELECT SUBSTRING(TextData, 1, 25)
FROM
ORDER BY Duration DESC.
(You will probably fiddle with the 25 so that you get enough of the SQL/SP call to know that the same query is being run, but not enough so that the actual parameter values are included -- you're trying to isolate poorly performing queries).
T1204 is only useful for deadlocking problems...which SQL Server resolves automatically.
P
May 18, 2004 at 7:09 am
Thanks. I'll give that a shot.
May 18, 2004 at 7:23 am
What I'll do.
1. defrag the index or dbcc reindex the DB
2. Update stats
3. check is the Optimize performance is set to 'background service' [Control Panel --> System]
4. How much memory you have ? > 3Gb is AWE PAE set
5. Is HTT on or off
6. How many cpu you have ? do you reserve cpu0 for Network I/O as stated in the cluster guide
7. Do you have Anti-virus on ? This is definitely a no-no on the SQL server. I saw 200% perf. degrad when NAV is running
8. what kind of H/W do you have ? I saw problem with cluster on some H/W does not perform will. The reason is they turn share array controller cache off. Is your controller cache on or off ?
I can tell you some H/W vendor have great prod. for stand alone server but justdoesn't work with cluster. This include some very fameous brand name. Bear in mind, vendor claim cluster certified does not gurantee the performance.
May 20, 2004 at 7:21 am
Download a tool I use for SQL management from Embarcadero. It'll give you a 2 week trial license (I think), which should be plenty of time for you to use the space and performance tools to locate your problems. It'll find all your fragmentation problems (plus reorg them for you) and give you a complete listing of all performance problems. Plus, it has a nice SQL analysis side to the Performance Analyst monitor that'll do all your SQL filtering for you. Use the Space Analyst and Performance Analyst utilities.
It's called DBArtisan Workbench. http://www.embarcadero.com
Hope it helps,
Rob
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply