Inconsistent Reads after tuning the query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Inconsistent Reads after tuning the query

Inconsistent Reads after tuning the query

Rate Topic

Display Mode

Topic Options

Author

Message

npranj

Posted Monday, December 10, 2012 10:30 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

Hi All,

I am very new to Performance tuning hence any help provided will be appreciated.

I hav a batch query which needed to be tuned for high Reads. I had the following observations -
1. RID Lookup
2. Key Lookup
3. A lot of nested CASE statements used in SELECT.
4. CASE Statement used in group by as well.
5. IsNull used arbitrarily.

I did the following -
1. I created covering index for two tables to get rid of the lookups.
2. Modified the CASE statements to reduce the nesting.
3. removed CASE from the group by section.
4. Removed unnecesary IsNull

Now when I check the profiler for Old (old query w/o indexx) vs New performance (updated query with indexes), I see the results vary with every execution.
The CPU time has reduced however not much difference in the Reads. In fact in the first run the CPU came down considerably bt the Reads shot up .

Can someone let me know how I can evaluate the % increase/decrease in performance?

Post #1394888

sanket kokane

Posted Monday, December 10, 2012 10:37 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 323, Visits: 960

Please Post both the queries along with the table structure and execution plan .. it will be more helpfull

-----------------------------------------------------------------------------
संकेत कोकणे

Post #1394890

npranj

Posted Monday, December 10, 2012 11:02 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

The batch is big. Can you let me know how how I can check the performance benefits.

Post #1394895

sanket kokane

Posted Monday, December 10, 2012 11:24 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:49 PM
Points: 323, Visits: 960

without DDL and Execution plan its difficult to say anything.

but still check the Estimated Cost in execution plan.

Run both the queries in single batch and compare execution plan
check which query having less cost

-----------------------------------------------------------------------------
संकेत कोकणे

Post #1394902

GilaMonster

Posted Tuesday, December 11, 2012 2:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635, Visits: 29,886

nidhis (12/10/2012)

The batch is big. Can you let me know how how I can check the performance benefits.

Compare duration, CPU and reads as well as the consistency of the performance.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1394961

npranj

Posted Tuesday, December 11, 2012 3:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

Thank you. Also - the plan changes when I use the same batch using sp_ExecuteSql
I see there are no Key Lookups when I run the same query using sp_executesql as opposed to when I execute the batch on SSMS.

The .net code uses sp_executeSQL.

Is my approach of picking the batch and running it separately on SSMS OK?

Post #1395012

SQLSACT

Posted Tuesday, December 11, 2012 4:34 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:44 AM
Points: 1,185, Visits: 2,099

nidhis (12/11/2012)

sp_executesql is gonna use a plan that is already in the plan cache, whereas executing the batch on it's own is gonna generate an execution plan, unless you're using forced parameterization.

Post #1395038

GilaMonster

Posted Tuesday, December 11, 2012 5:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635, Visits: 29,886

SQLSACT (12/11/2012)

sp_executesql is gonna use a plan that is already in the plan cache, whereas executing the batch on it's own is gonna generate an execution plan, unless you're using forced parameterization.

Not necessarily. Ad-hoc batches can use cached plans.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1395071

SQLSACT

Posted Tuesday, December 11, 2012 5:44 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:44 AM
Points: 1,185, Visits: 2,099

GilaMonster (12/11/2012)

SQLSACT (12/11/2012)

sp_executesql is gonna use a plan that is already in the plan cache, whereas executing the batch on it's own is gonna generate an execution plan, unless you're using forced parameterization.

Not necessarily. Ad-hoc batches can use cached plans.

Simple Parameterization?

Post #1395072

GilaMonster

Posted Tuesday, December 11, 2012 5:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635, Visits: 29,886

Parametrisation is not required for plan reuse. Parametrisation results in better reuse of cached plans, it's not a requirement for any having any reuse at all.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1395074

« Prev Topic | Next Topic »

Permissions