5
votes
2answers
358 views

Index seek much slower with OR condition compared with separate SELECTs

Based on these questions and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table Large table with historical data allocates too much of SQL Server 2008 ...
0
votes
0answers
53 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
1
vote
0answers
66 views

Different Execution Plans Picked for Similar Queries

Thank you in advance for reading it. This question is kind of long and need a little patience to read. I will provide as much details as I can to void interference factors to solve this problem. ...
1
vote
1answer
62 views

Print the execution time on messages Pane?

How I can print the execution time of each query on messages Pane? For example the Select * from tbl1 runs for 10 mins then Select * from tbl2 runs for 15mins and so on.. Select * from tbl1 Go Select ...
2
votes
1answer
91 views

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
1
vote
0answers
73 views

What to look for in query execution plans? [closed]

Assuming the query execution is slow, I have collected the xml plan. What should I look for in the actual execution plan. What are the top 10 things I need to watch out for? I know this is a broad ...
8
votes
1answer
252 views

Does SQL Sentry Plan Explorer count the reads in UDFs?

I have a query like this: select dbo.fn_complexFunction(t.id) from mytable t In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan in SQL Sentry to make the Query Plan include the ...
1
vote
1answer
191 views

Relative cost of two similar queries involving XML columns

I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column. I checked the execution ...
4
votes
1answer
944 views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
21
votes
3answers
6k views

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
10
votes
1answer
1k views

Does SQL Server 2008 store the creation date of execution plans?

We recently upgraded an application we use, which involved modifying the schema for the database. These changes could have forced cached execution plans to be discarded. If SQL Server was forced to ...
1
vote
1answer
131 views

SQl code performance

What measurement do you know if this sql code is efficient based on execution plans? How do you know if this sql code is efficient based on execution plans? SELECT [CustomerID], ...
13
votes
1answer
2k views

Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?

The three SELECT statements in this code USE [tempdb]; GO SET NOCOUNT ON; CREATE TABLE dbo.persist_test ( id INT NOT NULL , id5 AS (id * 5) , id5p AS (id ...
4
votes
1answer
129 views

Filtered indices: Why include the filtered-on field?

This just drives me mad. Consider a simple table with irrelevant columns removed: create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit ...
5
votes
1answer
2k views

Why does it take so long to calculate an Execution Plan?

One of our customers has just upgraded to a new server. For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second. ...
10
votes
1answer
4k views

Are there any risks to granting users SQL Server SHOWPLAN permission?

I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to ...
2
votes
2answers
489 views

Clustered monotonically increased index insert performance

I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it. I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this ...
3
votes
1answer
859 views

View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL ...
5
votes
1answer
384 views

how to improve performance by using or not using table variables

I have a table variable: DECLARE @to_process TABLE ( [Id] [bigint] NOT NULL, [SequenceId] [bigint] NOT NULL, ... ) INSERT INTO @to_process ( Id , SequenceId ... ) SELECT ...