Reputation
37,121
Next tag badge:
542/400 score
74/80 answers
Badges
8 97 156
Newest
 Enlightened
Impact
~1.4m people reached

Dec
24
comment NonClustered Index Scan Problem
CASE WHEN (1) is not valid TSQL. What is the actual predicate in the Query?
Dec
12
revised Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index
added 7 characters in body; edited title
Dec
12
reviewed Reject Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index
Dec
12
comment Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded
Your current query doesn't look correct. You are doing RANK() OVER (PARTITION BY det.Technology, det.XmitID ORDER BY DetectDate ASC, ReceiverID ASC) as DetID but then self joining on s1.DetID = sLAG.DetID + 1 AND s1.AnimalID= sLAG.AnimalID potentially joining rows from different partitions. I'd first fix the query and then look at altering indexes to avoid any need to sort at all. e.g. CREATE NONCLUSTERED INDEX [NIX_TCpi] ON [dbo].[Detections_with_Location] ( [XmitID] ASC, [Technology] ASC, [DetectDate] ASC, [ReceiverID] ASC ) INCLUDE ( AnimalID, [Data], [Units], [Location])
Dec
12
comment Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index
probably not in the OP's case. It works out at 2 reads per seek plus seven left over. So this must be an index with a single root page and leaf level pages straight under. To be two reads per seek including bookmark lookups would require the whole index to fit on a single page (1st read) and physical rid lookup on a heap (second read). And it doesn't seem likely that the index could fit on a single page when the base table is 14,000 pages (though I guess it could occur in theory if the heap contains empty pages that haven't been deallocated)
Dec
12
comment Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index
39sec to read 14,553 pages from cache (114 MB) seems extremely slow. Perhaps you are also encountering blocking from concurrent activity.
Dec
12
comment How to benchmark Microsoft SQL Server
Sqlio is no longer available and replaced with Diskspd
Dec
11
comment What does the “reads” column in sys.dm_exec_sessions actually indicate?
Maybe read ahead reads that were never actually needed by the query?
Dec
11
comment What does the “reads” column in sys.dm_exec_sessions actually indicate?
Yep. Could be a bug that is fixed between builds.
Dec
11
comment What does the “reads” column in sys.dm_exec_sessions actually indicate?
@srutzky - yes. The addition of a go changes things. i.stack.imgur.com/oQyuc.png
Dec
11
comment What does the “reads” column in sys.dm_exec_sessions actually indicate?
But if I don't use SQL cmd mode to connect so the connection hangs around without being automatically disconnected and then look from another session I do see non zero values so maybe just a delay in updating the data structure containing these.
Dec
11
comment What does the “reads” column in sys.dm_exec_sessions actually indicate?
Actually I see both columns zero with my approach on all editions I have tested from 2008 to SQL2016CTP3
Dec
11
comment What does the “reads” column in sys.dm_exec_sessions actually indicate?
On 2012 I'm frequently seeing 0 for both reads and logical reads despite non zero results reported from STATISTICS IO i.stack.imgur.com/XbHae.png
Dec
11
comment What is CP1 in SQL_Latin1_General_CP1_CI_AI?
I presume the 1 is for "Windows Latin 1"
Dec
10
comment Huge data and performance in SQL Server
Did you already experiment with delayed durability?
Dec
10
comment “String or binary data would be truncated” error copying nvarchar(10) to char(10)
Any triggers or computed columns on the table?
Dec
10
comment what is '#' in create table statement?
Why both the MySQL and SQL Server tags?
Dec
5
comment Could not open file control bank (FCB) for invalid file id 0 in database
What version of SQL Server are you on? If DBCC CHECKDB does not report any error and the problem continues to occur, you should contact Microsoft Technical Support for assistance.
Dec
5
revised Could not open file control bank (FCB) for invalid file id 0 in database
edited tags
Dec
4
revised Is there any drawback in altering an existing column datatype from TEXT to VARCHAR(n)?
deleted 6 characters in body; edited title