Simple Select * very slow
-
yes its fast if i restore it on the fast instance
-
but have you restored it on the slow instance? Try restoring it on the slow instance. Restore it using a different database name if you need to and try again with the select.
-
yes took a backup and restored on the slow instance still slow
-
where are you running the select from?
- iwatson wrote:
So, the differences in index size and free space there suggests that the two databases are not identical. Was an index rebuild run on one of them and not the other? Stuff like this could lead to differences in behavior.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Management Studio
- tripleAxe wrote:
where are you running the select from?
management studio
-
I suspect also fragmented db file or disk block size - more I/O to do to get the same data?
-
Management Studio on your client workstation or do you RDP on to each server? Could it be network? Try doing a "select * into another_table from table" or a temp table on both instances.....
-
Please, capture the wait statistics for the query on both fast and slow instances. Just running SELECT * over and over isn't going to tell us much unless we gather information. Get the I/O too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
i think i might have found something:
PAGEIOLATCH:
slow instace/db = waitcount 1620
waittime ms 282476
fast instance/db = waitcount 257
waittime ms 85164
-
That suggests a strong and distinct difference in I/O behavior. Are the different databases on the different instances on different disks?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Please try running update stats on the table with the recompile option on the slow DB and run the query again.
- BoobyB wrote:
Please try running update stats on the table with the recompile option on the slow DB and run the query again.
Sorry, I'm confused. "update stats on the table with the recompile option"? Update stats doesn't have a recompile option. Updating the stats will cause recompiles as queries get called after the update. You wouldn't need to hint the query to recompile. I'm just confused by what you're telling the OP to do here. Could you clarify?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Sorry, after reading it again , I might have confused the OP more.
Correction - .. Please try to re-run the select statement again but with adding the option (recompile) in the end of the query.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply