July 29, 2014 at 11:08 am
I have a weird issue..
There were a few tables i had replaced with new data.
the table structure is same just data has been refresh recently. New and the old data look almost same with few minor chnages to the values
So i rename the old table to <table>_old
And then recrete these table with new data
I also create all the indexes from old table to new one..
However the performance of my queries is too slow after the chnage.. Same queries worked well with old table..
Could someone please indicate wheat i might be missing
I have also tried rebuilding all the indexes
There was one clusterd and 2 non clustered indexd on each of the tables
Please help!!
July 29, 2014 at 11:26 am
Please post the execution plan and DDL.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2014 at 12:53 am
Execution Plan or IO statistics of reads and writes must to have a look at in, also please verify what file group u have placed the new table and check the file group of old table...
July 30, 2014 at 8:04 am
It could just be that your statistics were out of date on the old tables and the new tables, with more up to date statistics, are generating new execution plans. I'd suggest running the query against the old and new tables, capture the execution plans, and compare them. Specifically, look to the estimated number of rows and executions for the operations.
Also, check the foreign key constraints on the old tables. Maybe they were created with NOCHECK. That could also lead to differences in the plans.
In general, in order to understand the choices made by the optimizer, you need to look at the execution plans, not just blindly rebuilding indexes.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply