April 29, 2010 at 4:33 am
Hii frndz
I m new to TSQL , can anybody help me in optimizing the attached query.
SP
Table structure
and execution plan is attached here
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
April 29, 2010 at 4:39 am
How many rows does the query return? How long does it currently run? How many rows are in the tables?
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
April 29, 2010 at 6:36 am
First thing I noticed is that you have two table scans, not clustered index scans. Unless you have a very good reason, most tables should have a clustered index. I'd put a cluster on OIMS_ORDER_ALERTS, probably with a leading edge on the OrderID column (not sure if it should have a compound key or not, I didn't dig that far). That should remove the scan and the order operation that immediately follows it.
The scan against the OIMS_ORDER_MASTER cluster doesn't make sense because [OIMS_ORDER_MASTER].[ORDER_DATE] you have an index with that column on the leading edge (the front, or first column), so you should be getting an index seek but instead you're getting a cluster scan. I would have expected a key lookup operation. I'd check the distribution of that data, how many unique values do you have in that column, becuase I suspect the index must be very non-selective (few unique values).
That's just a few ideas off the top to get you started.
"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
April 29, 2010 at 10:44 pm
Thanx a lot for ur replies
Here in this case ,the query provided here is getting fired after every 5sec(Which is refreshing & giving data to user after every 5sec)
OIMS_ORDER_MASTER -- Daily flushes --Max count at day end = 67
OIMS_ORDER_ALERTS_ADMIN -- Daily flushes--Max count at day end = 10
OIMS_ORDER_ALERTS -- Daily flushes--Max count at day end =1000
VWAP_SCRIP_MASTER -- Max count at day end = 6000
This query runs through SP attached here also it creates problem between 2:00 PM to 3:30 PM
(Blocking each other & other queries too)
Server Config.
CPU 16 core
RAM 16gb (SQL server 10GB )
Windows server 2003 SP2
Sql server 2005 sp3(4262 hotfix)
all RAID1 disks
Also i found some BufferLatch wait type 2:1:.....
so i created 8 tempdb files on single drive(other than mdf ,ldf,OS drives)
it resolved the blocking issue but up to certain extent when load increases this problem occurs again but not worst as it was before creating multiple tempdb files.
then i observed this SP "OIMS_ORDER_GET" (sply the query provided )takes high CPU and remains in queue blocking each other.
should i create more tempdbs on another drive(What will be the -ve implications)?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
April 30, 2010 at 6:16 am
No, don't create more files. The issue is the code and the indexes. That is almost always the issue. You can only temporarily solve most problems by throwing more hardware at them. You need to address the shortcomings in the code. Some of the suggestions I made are a decent place to start. I'm sure there are other things you can do with drill-down.
"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
May 7, 2010 at 6:22 am
can i revert back to my original tempdb?
i.e now i have 8 tempdb files, can i revert back to 1 tempdb file?
if yes, how?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 7, 2010 at 6:46 am
You should be able to use the processes outlined here to rebuild the tempdb with a different structure. It'll work the same way as if you were moving it.
"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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply