1,076 reputation
211
bio website
location Altanta Georgia
age 36
visits member for 1 year, 9 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?
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
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
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
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
comment Mapping a shared physical folder to filestream
What OS version is on the sql box and the file server?
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
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.
Jun
11
comment After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB
sounds like lock escalation..... (blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/…)
Jun
5
comment How database administrators can see my requests to SQL Server?
don't for get about Extended Events.
May
22
comment How would I change this relational model to fit a nosql database?
I'd start with a 1-to-1 with what you have, and only try something different if you can show that it performs better. Also, due to the maximum size limit of a document in MongoDB you may be limiting yourself if you denormalize too extensively.
May
22
comment How would I change this relational model to fit a nosql database?
Why are you switching from a known scaleable design that could easily handle 10's of thousands of queries per second (without sharding) and forcing it into a nosql solution? It is very unfortunate if it truly is "beyond your control".
May
16
comment Can I use the database and query it while full-text indexing?
for fulltext, no. All editions are online. however, you will get partial results while the index is being rebuilt.
May
10
comment Concurrent Access causing deadlock on the same table
Can you add the rest of the deadlock graph? Also, the select query wouldn't take any kind of "X" lock (X, IX, SIX) so the insert query is deadlocking on something besides the select. Ideally, turn on traceflag 1222 and capture the deadlock in the errorlog as it provides more detailed information.
May
9
comment Connection pools being reset with Error: 18056, Severity: 20, State: 46. & Perfmon Counters not showing
What kind of auth are you using? (Sql Logins, Kerberos, NTLM?) SELECT s.session_id, host_name, login_name, c.auth_scheme FROM sys.dm_Exec_connections c INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id