1,046 reputation
211
bio website
location Altanta Georgia
age 36
visits member for 1 year, 8 months
seen May 17 at 11:14

I'm the Sql Server DBA for a "Top 300" US website. My areas of expertise are full-text search, sql internals, troubleshooting performance issues and scaling up. I'm passionate about Sql Server and I'm not scared of the debugger.


Mar
31
comment What type of fact table / loading solution for a reservation system?
What is the grain of your fact table?
Dec
3
awarded  Yearling
Jul
14
comment Audit Queries in SQL Server 2008 Including Start Stop Times
Extended events have lower overhead than server-side traces, so start there.
Jul
14
answered Fulltext search limiting users possibilities
Jul
12
comment linked list in SQL and Trees
oops! my apologies...@user16484 can you update your question to mention the change from list to tree? Also, what other types of queries do you expect? (you mentioned given a node, get all grandchildren)
Jul
12
revised linked list in SQL and Trees
added reference to hierarchyid
Jul
12
comment Don't use a transaction for Stored Procedure
To be clear (and for the lazy who don't want to click the links), you aren't actually starting another transaction. Aka the title on Paul's post: "Myth: Nested Transactions are real." They aren't real transactions. COMMIT in a nested transaction does nothing except decrement @@TRANCOUNT. It's true that you don't get an error if you nest BEGIN TRAN/COMMIT, but that is different from having real nested transctions.
Jul
12
answered linked list in SQL and Trees
Jul
12
comment Don't use a transaction for Stored Procedure
If the SP is called from within a transaction, then saved transactions above are the answer. If the sp isn't called with, then @mrdenny is correct. Sql server doesn't support nested transaction.
Jul
10
comment Check existence with EXISTS outperform COUNT! … Not?
You have too few records to get a meaningful comparison. The plan used by the optimizer will change as cardinalities increase. Also using temp tables vs table variables could change plans as well when statistics are considered.
Jul
2
comment Partitioning / indexing an extremely large table
What is the average size of a row? With heaps the inserts will get put into whatever page they fit on (done via scanning the PFS byte map) With clustered indexes there is exactly one place the row can end up, so depending on your data distribution it could account for some of the difference.
Jun
27
answered SQL Server fsync Options
Jun
27
comment Mapping a shared physical folder to filestream
What OS version is on the sql box and the file server?
Jun
19
answered Can Availability Groups replace transactional replication?
Jun
19
comment Nonclustered index is faster than clustered index?
Nothing you've said is incorrect. However, OP is "practicing indexing" not "learning how queries are costed" so I answered in terms of indexes and their physical size. We both will agree that in most cases the real world performance difference is definitely not 62%/38%. My answer to the question in the title: "Yes, the NCI is smaller than the CI, therefore is faster to scan." IMHO, outright discouragement of using query costs as a proxy for relative performance is the advice we should give by default. (However, that's a discussion for META)
Jun
19
comment Nonclustered index is faster than clustered index?
Please re-read the first sentence in my answer. Comparing costs directly is meaningless. For the performance difference between the OP's queries, a better estimate can be derived empirically by calculating the reduction in the size of the indexes (and therefore the number of IO's), not by the costs from the optimizer.
Jun
18
answered Nonclustered index is faster than clustered index?
Jun
17
comment Which design to choose in this case?
What rdbms? Are you just storing those vectors or will you need to do calculations on then in the db?
Jun
12
comment After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB
@MarkStorey-Smith yes, conversion is when locks change type. Lock escalation is a two-step process, table IX->X lock conversion and multiple row (or page) X locks gets released. The only time intent-locks get converted to non-intent locks is during lock escalation.
Jun
12
comment After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB
@MarkStorey-Smith Intent locks (IX and IS) deadlocked when converting to X and S locks.... that's lock escalation.