June 2, 2008 at 10:35 am
Well, I should have proposed that from the beginning, but have you already tried to run Tuning Advisor with your query?
Since your original question was not now to improve the query, just let me know if you do not seek any more thoughts here.
These PK columns, are they surrogate keys? Are they identity cols? Are they used in other joins?
What about the column CaseID, I assume that case id should at least be highly selective in the enquiry table, if not even unique. Similar for the other two tables, how selective are these caseid cols?
Would it make sense to include them in indexes (as first col)?
(I dont yet understand why you have the two indexes which are sharing xref, the second one should be enough?)
Best Regards,
Chris Büttner
June 2, 2008 at 11:29 am
Jeff Moden (5/30/2008)
First, I think I'd use UNION ALL... considering the source tables, there's probably not much chance of duplication, anyway....
I considered mentioning UNION ALL as well, but I assumed that there was the possibility that a ClosedEvent could potentially be in both tables. If not then I would use UNION ALL.
You could also remove the Index that is only on XREF1 as the second index which is XREF1 and ObjectType would be sufficient for queries only filtering on XREF1.
You could also use a hint to force the query to use the composite index.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 3, 2008 at 4:00 am
Tried rewritng the view without the union all and various index combinations but did not show much improvement. Decided to look at what the server was doing during the query and record fn_virtualfilestat figures and waitstats, got the following:
Wait type Wait Time Percentage
PAGEIOLATCH_SH 8229205.0 63.6
CXPACKET 1924036.0 14.9
WRITELOG 841092.0 6.5
LATCH_EX 808910.0 6.2
PAGEIOLATCH_EX 625666.0 4.8
LCK_M_S 262499.0 2.0
Reads/Sec Writes/Sec I/O Count I/O /sec IoStallMS/OP
3.62 .30 236 3.92 49
14.08 1.75 952 15.83 36
11.46 1.08 754 12.54 51
113.48 .12 6816 113.59 13
270.28 1.48 16314 271.76 8
Any one want to comment on the above stats ?
Ronnie
June 3, 2008 at 9:51 am
Probably don't have enough RAM on the box (or allocated to SQL Server). Way too many read-aheads. Disk system seems slow too, but that could be just because it is having to work too hard due to low RAM.
Read throught he thread quickly, but have you tried a WITH RECOMPILE option for the query? Maybe parameter sniffing is getting a bad plan?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 3, 2008 at 12:38 pm
Was able to run some test on the server with no other users active, the timings where:
(3323 row(s) affected)
Table 'LGNCC_CLOSEDCASEEVENTS'. Scan count 1, logical reads 19539, physical reads 2, read-ahead reads 19612.
Table 'LGNCC_ENQUIRY'. Scan count 2, logical reads 9598, physical reads 1302, read-ahead reads 7.
Table 'LGNCC_CASEEVENTS'. Scan count 1, logical reads 5076, physical reads 2, read-ahead reads 5107.
SQL Server Execution Times:
CPU time = 1532 ms, elapsed time = 96367 ms.
(3323 row(s) affected)
Table 'LGNCC_CLOSEDCASEEVENTS'. Scan count 1, logical reads 19539, physical reads 0, read-ahead reads 0.
Table 'LGNCC_ENQUIRY'. Scan count 2, logical reads 9810, physical reads 0, read-ahead reads 0.
Table 'LGNCC_CASEEVENTS'. Scan count 1, logical reads 5076, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1140 ms, elapsed time = 2905 ms.
When no activity on the box the second query is a lot faster with the time dropping from 96 seconds to 3 seconds. However during the day a second run of the query will take as long as the first run or even longer. So I am inclined to think I have either a memory shortage problem or an I/O problem or both.
Ronnie
June 3, 2008 at 9:05 pm
3 to 96 seconds is probably more than just slow IO. I would be looking for blocking. sp_who2 active is the quickest way to detect that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 4, 2008 at 4:03 am
Ran sp_who2 and did not see any blocking, however during the running of the query which took over 90 seconds sp_who2 reported that the spid was sleeping a lot of the time rather than runnable.
Ronnie
June 4, 2008 at 9:34 am
Perhaps parallelism is hurting here. Check for CXPACKET waits using waitstats analysis. Do you have hyperthreaded cpus by any chance? Also try running the query with OPTION (MAXDOP 1) to see how a single-threaded plan works out.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 4, 2008 at 2:11 pm
It does not appear to be running in parallel, and setting maxdop to 1 makes little difference.
I ran the query last night when the box was quiet and it completes in 3-5 seconds. Even with multiple queries running at the same time it still completed in 3-5 seconds with no physical reads after the first query completes.
However I ran it today and it took 300+ seconds to complete on several occasions. Every time it was run it performed physical reads, but the cpu time was constant at around 1.5 second.
When the box is quiet the data stays in the buffer pool, but when it is busy it is removed almost immediately. The server has 3.5GB of memory and SQL server is configured to use 2GB.
During the runs I used sp-who2 to check for blocking but none was evident.
I am now at a loss as what to do next to try and resolve what is going on.
Ronnie
June 4, 2008 at 2:18 pm
Time to start tuning queries - ANY that may be doing table scans on large tables that could otherwise be doing index seeks/bookmark lookups. I have seen any number of situations where poor tuning (esp indexing issues) causes horrid performance on the entire server.
Oh, and do what you can to get more RAM available. 🙂 Since SQL2k, you will need enterprise edition (in which case you could go up to 3GB on this hardware using /3GB switch) or upgrade to 2005 std. Better hardware will obviously help if you go with 2005!
I suppose you could upgrade your IO subsystem too, to speed the flow of data to/from the disk. But RAM is KING when it comes to perf.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 4, 2008 at 2:26 pm
AS Jeff Moden mentioned earlier, it could be a network issue as well. Are there backups across the network running at this time? You can test this a little by setting Query Analyzer to "Discard Results after execution". Here is a link that discusses how: http://msdn.microsoft.com/en-us/library/aa216929(SQL.80).aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 5, 2008 at 12:20 am
Guys,
Thanks for all the advice. I have asked for more traces from the server which will be done tomorrow so I will see if there are any other queries hitting the system hard.
The problem is this server runs multiple databases, and when I ask for a trace they will only give me a trace from our application. So I do not get to see what the other databases are doing.
They also say it is only the users of our application that are complaining about performance.
Ronnie
June 5, 2008 at 6:18 am
1) I bet there are plenty of things in your database that can be tuned. Haven't found a client yet where that wasn't the case!! :w00t:
2) Do a differential IO Stall analysis using fn_virtualfilestats. If you see lots of waits on a particular drive let your admin people know.
3) Did you check for index frag? What about OS file frag?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 5, 2008 at 12:57 pm
Hi,
The customer will not give me access to the sql server cluster, the reason being that it runs other applications to the one I support.
I get information in small amounts, like users are compalining that your application is slow, but other users of other applications on the server are not complaining.
I have now got information to indicate that this cluster is running 20 other sql server applications as well as ours.
How do you find a problem where between 8am-9am the application is in use and performance is fine, then from 9am on it grinds down, but according to the customer its your application is the issue not one of the other 20.
Surely if our application is getting slower, and is the cause of the performance issue, be it cpu/memory or IO bottlenecks, then it should have a knock on effect on the other applications ?
Ronnie
June 5, 2008 at 1:03 pm
Ronnie (6/5/2008)
Hi,The customer will not give me access to the sql server cluster, the reason being that it runs other applications to the one I support.
I get information in small amounts, like users are compalining that your application is slow, but other users of other applications on the server are not complaining.
I have now got information to indicate that this cluster is running 20 other sql server applications as well as ours.
How do you find a problem where between 8am-9am the application is in use and performance is fine, then from 9am on it grinds down, but according to the customer its your application is the issue not one of the other 20.
Surely if our application is getting slower, and is the cause of the performance issue, be it cpu/memory or IO bottlenecks, then it should have a knock on effect on the other applications ?
Ronnie
Well one thing I could see making you app slow and not others could be some type of scheduled job on your app's database. Of course - why a big job would be running at that time might be something to ponder, but something like a regularly scheduled data load/import firing at the wrong time could substantially slow/stall performance for a set time, and then clear itself up...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply