April 7, 2008 at 7:28 am
hi,
Iam trying to improve the performance of the following query which is taking 7 min to execute.
IF EXISTS(SELECT * FROM SYSINDEXES
WHERE CAST(OBJECT_NAME(id) AS VARCHAR(30)) = 'cc_exposurerpt'
AND name = 'Retired' AND INDEXPROPERTY(id, name, 'IsStatistics') = 1)
could you plz give some idea to increase the performance of this query
thanx
April 7, 2008 at 8:25 am
You must be experiencing blocking or something else on the server. This query, as you have it defined, takes 1 ms to run on my laptop (not exactly a screaming speed demon piece of equipment, let me assure you). Also, if you look at the execution plan, it's a simple clustered index seek against sysindexstats. There must be something else going on with your system. Unless that's an incomplete query because you've only posted the IF EXISTS statement with no follow-up on what happens if something does exist.
"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 7, 2008 at 8:53 am
I agree with Grant. you could remove the CAST and functions (look up the ID first), but that shouldn't cause a huge problem on any machine.
Even a big server, with thousands of indexes, shouldn't take long to scan.
April 7, 2008 at 12:06 pm
the query i got is from profiler output,so how can I get that full query?
April 7, 2008 at 1:24 pm
Profiler output is to a text column, but if you're selecting from a text column in a TSQL Query, you might not be seeing all the data (there's a limit to the data returned in the SSMS options). That would explain why the query is truncated. Try changing the output or pulling it from a file or directly off a Profiler window.
"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 10, 2008 at 3:35 pm
madhu.arda (4/7/2008)
the query i got is from profiler output,so how can I get that full query?
if you know the spid no, you can run this:
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sys.sysprocesses WHERE spid = XXX -- put actual value here
SELECT * FROM ::fn_get_sql(@Handle)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply