May 21, 2013 at 8:56 am
I have a situation that i thought was impossible and cant really explain to the users. I was asked to speed up an application. The database have no indexes and no PK/FK but have one maintenance job. Each week a shrink database is executed.
When asked, i got told that was to speed up the query. I desactivated that job as soon as i saw it, but after 5-6 days the query were getting slower. I got asked to do a shrink, and i did to show them that it will not help, but the query ran faster.
Now i do not ask for a solution, adding indexes and no more shrinking will do the job...but im wondering what is going on and under what condition a shrink can actually help performance.
Thank you
May 21, 2013 at 9:49 am
Shrink alone can't speed up a query. Probably something else going on as well.
No indexes and no primary key means every single operation is going to be a table scan. That's likely going to be IO bottlenecked.
Maybe some strange SAN behaviour where the shrink causes a lot of IOs and the SAN moves the data file onto faster storage as a result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2013 at 11:00 am
I'd agree with Gail. This doesn't make sense, but I suspect it's a coincidence somehow and not really correlation. Adding indexes is likely to be the best way to speed things up.
May 21, 2013 at 11:14 am
I was pretty sure that it's not possible, but i'm not an expert, so i needed a little confirmation first.
I think i'll go with the coincidence explanation. Thank you two for your time!
May 22, 2013 at 11:54 am
hi2u (5/21/2013)
I was pretty sure that it's not possible, but i'm not an expert, so i needed a little confirmation first.I think i'll go with the coincidence explanation. Thank you two for your time!
IIRC, shrinking a database will flush the procedure cache, which could result in better plans for queries as they get fired again for the first time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 22, 2013 at 12:22 pm
TheSQLGuru (5/22/2013)
IIRC, shrinking a database will flush the procedure cache, which could result in better plans for queries as they get fired again for the first time.
It doesn't, I just checked to be sure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2013 at 12:37 pm
GilaMonster (5/21/2013)
Shrink alone can't speed up a query.
Isn't that an overly broad assertion? If, for the sake of argument, some/all of the disk extents added were scattered all over the drive(s), and a shrink released them, couldn't that theoretically speed up the disk access enough to make a query run faster?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 22, 2013 at 12:40 pm
ScottPletcher (5/22/2013)
GilaMonster (5/21/2013)
Shrink alone can't speed up a query.Isn't that an overly broad assertion? If, for the sake of argument, some/all of the disk extents added were scattered all over the drive(s), and a shrink released them, couldn't that theoretically speed up the disk access enough to make a query run faster?
It is if you take it out of context:
GilaMonster (5/21/2013)
Shrink alone can't speed up a query. Probably something else going on as well.
Gail did say that there is probably something else going on as well.
May 22, 2013 at 1:40 pm
I'd be quite concerned with a database with no pri keys or indexes and I'd be running profiler to capture all longer running queries and getting some testing done on indexes ASAP.
May 22, 2013 at 2:04 pm
ScottPletcher (5/22/2013)
GilaMonster (5/21/2013)
Shrink alone can't speed up a query.Isn't that an overly broad assertion?
Only when you quote me out of context.
If, for the sake of argument, some/all of the disk extents added were scattered all over the drive(s), and a shrink released them, couldn't that theoretically speed up the disk access enough to make a query run faster?
Not very likely, and only if the read was from disk, in which case the fragmentation introduced by the shrink would have a counter effect of slowing down the IO, since fragmentation affects range scans from disk, which are the only types of scans one will be doing with no indexes of any form on the tables.
Besides, that's one hell of an edge case and if that were the case in some particular system, a disk defrag would do a far better job of getting all the segments of the data file back together. Follow that with an index rebuild and you'd have nice contiguous indexes in a nice contiguous file.
Additionally, some SAN designs intentionally scatter files all over the drives with the idea there being that since the segments of the files are not contiguous, different drive heads can pick them up at the same time, essentially scaling out the IO operations. Only works when you have lots and lots and lots of drives though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2013 at 2:15 pm
GilaMonster (5/22/2013)
ScottPletcher (5/22/2013)
GilaMonster (5/21/2013)
Shrink alone can't speed up a query.Isn't that an overly broad assertion?
Only when you quote me out of context.
If, for the sake of argument, some/all of the disk extents added were scattered all over the drive(s), and a shrink released them, couldn't that theoretically speed up the disk access enough to make a query run faster?
Not very likely, and only if the read was from disk, in which case the fragmentation introduced by the shrink would have a counter effect of slowing down the IO, since fragmentation affects range scans from disk, which are the only types of scans one will be doing with no indexes of any form on the tables.
Besides, that's one hell of an edge case and if that were the case in some particular system, a disk defrag would do a far better job of getting all the segments of the data file back together. Follow that with an index rebuild and you'd have nice contiguous indexes in a nice contiguous file.
Additionally, some SAN designs intentionally scatter files all over the drives with the idea there being that since the segments of the files are not contiguous, different drive heads can pick them up at the same time, essentially scaling out the IO operations. Only works when you have lots and lots and lots of drives though.
It wasn't "out of context" -- you made an absolute statement, which thus applies to any context.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 22, 2013 at 2:23 pm
ScottPletcher (5/22/2013)
It wasn't "out of context" -- you made an absolute statement, which thus applies to any context.
An absolute statement that you chose to ignore parts of.
I said shrink *alone* cannot improve performance. Emphasis *alone* (which you apparently chose to ignore). If a shrink runs and after the shrink there is improved performance, then there is something else going on (which I said and you also chose to ignore), possibly that the shrink triggered (SAN auto-tiering for a quick example) that resulted in the improved performance.
If you really have nothing better to do than nitpick things I say, can I perhaps recommend some good books for you to read?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2013 at 2:30 pm
GilaMonster (5/22/2013)
ScottPletcher (5/22/2013)
It wasn't "out of context" -- you made an absolute statement, which thus applies to any context.An absolute statement that you chose to ignore parts of.
I said shrink *alone* cannot improve performance. Emphasis *alone* (which you apparently chose to ignore). If a shrink runs and after the shrink there is improved performance, then there is something else going on (which I said and you also chose to ignore), possibly that the shrink triggered (SAN auto-tiering for a quick example) that resulted in the improved performance.
If you really have nothing better to do than nitpick things I say, can I perhaps recommend some good books for you to read?
My point was that I thought it was theoretically possible for a shrink ALONE to improve the performance of heaps -- as all tables are in this case -- under specific conditions. You still claim that's absolutely impossible; I think there could be specific cases where it could theoretically happen, depending on the DELETE/INSERT patterns to the heaps and the specific disk allocations.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 22, 2013 at 2:57 pm
Would a shrink maybe put more of the database into the cache?
May 22, 2013 at 2:58 pm
ScottPletcher (5/22/2013)
I think there could be specific cases where it could theoretically happen, depending on the DELETE/INSERT patterns to the heaps and the specific disk allocations.
Cool, when you find one, be sure to write a white paper up on it. It would be an interesting example of how shrink interacts with other factors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply