November 24, 2008 at 9:15 am
Hi to all,
Can any one throw some light to my query.
I have a select query which is extarcting from around 10 tables and it contains all the inner joins and left outer joins.
My concern is the above mentioned query is retriving the data in 10 -15 mins on some days and the same query is not at all giving the results even after running for more that 2 hrs. Even there is no blocking at that moment.
I haven't tuned the query and now it is working fine and displaying the results in 10 -15 mins.
The actual problem is the issues occurs intermitetently.
Is there any way to troublre shoot the problem and to know the root cause so that the query should be able to display the results in the expected time.
Thanks,
Sandhya
November 24, 2008 at 9:24 am
In general, you could run a trace on the server, and find out what, if anything, is running at the same time or right before it.
For anything more specific, I'd really have to see the table structure and the code of the query. You can post that here, if you're allowed to (some companies don't allow that kind of thing).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 24, 2008 at 10:52 am
Are you using right indices, i can understand that you said that it runs slower on times, check with profiler and see is there any other process that is running on the same time, is there any linked server involved in this SQL, if so check other servers as well as well as network 🙂
November 24, 2008 at 12:39 pm
If your situation allows dirty reads, then use table hints. With the (nolock) table hint, no shared locks are issued and exclusive locks are ignored.
Also, you would want to see "sys.dm_db_missing_index_details" dmv to find out if this has any record of any missing indexes.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 25, 2008 at 7:47 am
Hi to all,
Thanks for the prompt response.
In my select query no linked servers are there and we are using the NOLOck options also.
Below is sample example of my query.
SELECT DISTINCT col1, col2,col3, col4,col5, col6. Col7, col8,col9,col10,
Col11,col12, col13, col14, col15, col16, col17, col18, col19 col20
FROM Table1 T1 (NOLOCK)
join Table2 T2 (NOLOCK)
on T2.col10= T1.col10
join Table3 T3 (NOLOCK)
on T3.col10 = T1.col10 and T3.col11 = T2.col11
join (SELECT Col21, Col22 Col23
FROM XYZSERVER..Table4 (NOLOCK))
T4 ON T3.Col21 = T4.Col21
join ABCSERVER..Table5 T5 (NOLOCK) on T5.Col24 = T3.Col24
As you said that check for the proper indexes, if this is the case it should not return the data at all times. So i hope this may not be the genuine problem.
No other process are running at that time.
Can any one look into this and help on this issue.
Sandhya.
November 26, 2008 at 1:57 pm
Try this query...
SELECT
t.[name] AS objectName,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM
sys.dm_db_missing_index_details mid (nolock)
INNER JOIN sys.tables t (nolock)
ON mid.[object_id] = t.[Object_id]
WHERE t.name LIKE 'LU_CHNL_LVL'
This would give you a list of column names that the SQL server engine actually looked for at least once before hitting the table directly. This would be helpful in deciding in which index to create. Reading the BOL would be helpful for this http://msdn.microsoft.com/en-us/library/ms345434.aspx.
Also, you can get the usage statistics of these missing indexes from "sys.dm_db_missing_index_group_stats" DMV.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 26, 2008 at 2:09 pm
shiva challa (11/24/2008)
With the (nolock) table hint, no shared locks are issued and exclusive locks are ignored.
Using nolock doesn't just allow dirty data. It essentially says to SQL that it doesn't matter if the data isn't 100% accurate. It's possible to miss rows or read rows twice or more.
Be careful with nolock. It should not be mindlessly applied to every query.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply