66,378 reputation
988169
bio website sqlsentry.com
location Rhode Island
age 40
visits member for 3 years, 8 months
seen 6 hours ago

Senior Consultant at SQL Sentry, SQL Server MVP, @AaronBertrand on twitter, blogger at sqlperformance.com, blogs.sqlsentry.com, and sqlblog.com, Mac user, volleyball player, curler, hockey/football/tennis fan, big-time loser at fantasy football, proud father of two beautiful girls, and a Canadian, eh?


1d
comment Error on DDL statement never invokes CATCH
Not really a fair comparison, since compilation inside dynamic SQL is deferred until runtime by definition. In fact I bet a good portion of dynamic SQL in use today is precisely as a trick to get around compile-time errors.
1d
comment Error on DDL statement never invokes CATCH
Yes, you're right, sorry. Deferred name resolution is something I wish there were a global option to just turn off. sommarskog.se/strict_checks.html
1d
comment Query execute slowly when changed compatibility Level of my database
Are you making a decision to use a regression-correcting trace flag globally (or keep an old compatibility level for the entire database) because of a regression in one stored procedure? Why not just use the trace flag to set the old CE for individual queries that are showing regressions, and leave it alone for the rest?
1d
comment Error on DDL statement never invokes CATCH
In fact doesn't the error occur during compile time, not run time? In which case, there is no TRY, CATCH, or spoon.
2d
comment Size of Table Now vs 2 Years ago
Just keep in mind that the new table won't have two years' worth of fragmentation / rebuilding etc., and make sure that it has all of the same indexes, compression settings, what have you.
Nov
20
comment SQL Server spending a lot of time creating sp_dbmmonitorupdate
I agree with @Kin - are you actually using the data this thing is collecting for you? If not, shut it off.
Nov
20
comment SQL Server spending a lot of time creating sp_dbmmonitorupdate
You're looking at stats over 5 months. Are you sure that CPU time is actually statistically significant? Aside from wait stats, have you done anything to point to actual slowness or CPU usage (like actual CPU on the box in real time, not CPU time aggregated over months) that can be correlated specifically to this procedure?
Nov
20
comment Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”
Haven't tested any impact at that level, so not sure, sorry. Performance certainly wouldn't be my top priority here.
Nov
20
comment Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”
IMHO (and not everyone will agree), constraints should be as close to the data as possible. Why? Data changes don't always go through your application (and it's next to impossible to guarantee that even if you try).
Nov
20
comment View Constraints in SSMS
Did you try right-clicking to see what the context menu might contain? Things like Script Constraint As >?
Nov
20
answered Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”
Nov
20
comment View Constraints in SSMS
Did you notice the long list of constraints in Object Explorer on the left? Why do you need to jump through hoops in the horribly buggy table designer to find them? Also, you are looking at a check constraints dialog. Are you sure this table has any check constraints? If the default system names for your defaults are any indication, they would be above those, so it doesn't appear there are any. So I would guess that the dialog is blank because there are no check constraints...
Nov
20
comment What could cause an auto increment primary key to skip numbers?
@Steve You can use a serializable transaction to make the next row max(current id) + 1. But how are you going to prevent gaps? Never let anyone delete anything ever?
Nov
20
awarded  Notable Question
Nov
20
comment Where's my missing Sql Server Express 2005 Database space? Size and space available dont add up to SQL express size 4G?
Where are you seeing 4 GB? Have you looked at the physical log file (.ldf)? What exactly are you measuring when you say this database is 4 GB?
Nov
19
comment How do I remove duplicate records in a table?
@AA.SC yep, I think my edit beat your comment.
Nov
19
revised How do I remove duplicate records in a table?
edited body
Nov
19
revised How do I remove duplicate records in a table?
added 44 characters in body
Nov
19
answered How do I remove duplicate records in a table?
Nov
19
comment Is it normal for tempdb to not give up disk space when it releases things?
Simple recovery means that the log space within the log file is more readily reused. It does not mean the file size itself will magically shrink (and you don't want that). Please read this in its entirety.