64,639 reputation
886168
bio website sqlsentry.com
location Rhode Island
age 40
visits member for 3 years, 7 months
seen 41 mins 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?


56m
comment Re-writing a sub-select as a condition on a column generated with 'over (partition by …)'
Clever but naughty. "Do not use this" should be in 400pt red font. :-)
1h
comment MDF/TempDB Growth, SQL Server 2005 Enterprise
Are you sure it was the mdf file and not the log file? If it was the log file, see this post - it is about deletes but applies to any DML really. Also is your T-SQL script adding 1.8 million rows, one insert at a time?
4h
comment Sql Server 2008 R2 - Failed to send an environment change notification to a log shipping partner node
(Oh, and you should also check your connection strings. Do they have things like failover partner? This could actually be what is telling SQL Server to go validate the login on the partner (and yes, I realize that is for mirroring, but this wouldn't be the first time SQL Server's error messages could be misleading).)
4h
answered Re-writing a sub-select as a condition on a column generated with 'over (partition by …)'
4h
comment Merge Problems SQL Server
As an aside, please read this. MERGE sounds great until one of these issues bites you. Hard.
4h
comment how can I track down the source of “Internal connection fatal error”?
Sounds like you have flaky connectivity then. Are you connecting to the SQL Server machine over your home WiFi? Do you have a domain? How are you connecting to the box (TCP/IP, named pipes)? Are you using IP address, server name, FQDN, ...? What do you see in the actual SQL Server error log?
5h
comment What is a non-clustered index scan
No, I did not say that. A coin toss could always land heads, and SQL Server might always choose the NC. I don't know of any rule that guarantees SQL Server will always choose the NC. If that's what you're looking for, you've come to the wrong place.
5h
comment Re-writing a sub-select as a condition on a column generated with 'over (partition by …)'
@ajeh The idea is to not use the sub-queries. Why? Pointy-haired boss think that all subqueries are bad? As aasim stated, you can't use the alias of an expression like rank() or row_number() in the WHERE clause. So maybe you should go back to the drawing board and explain what problem you're actually trying to solve by removing any and all subqueries...
5h
comment What is a non-clustered index scan
@Pavel way too broad. Can you narrow down the scenario just a little bit, please? Anyway, I still think you're out in left field with "cost is not relevant" - in cases where an NC is preferred over CI, what factor(s) do you think make it preferred, if not cost?
5h
comment Effects of renaming FC and using its original name for AG network name
In my experience most people have left that stuff alone, and documented very well that the original primary server was not renamed when they expanded geographically. You could also leave it the same and use DNS or hosts file to let the applications and other servers/locations think the server was renamed, when really it wasn't. The actual rename is the hard part IMHO, and I would be looking at any alternative I could to avoid doing that.
5h
comment Effects of renaming FC and using its original name for AG network name
An alternative you haven't considered is DNS. Obviously changing the connection string is easiest, but if that is a problem for some reason, you could leave the connection strings alone and remap using DNS. More importantly, why do you need to rename the server/cluster? Do you really need the geographical location in the name? Will it really make sense to refer to it that way, since it seems logical that part of the HA/DR topology might be to fail over to a different geographical location?
5h
comment Sql Server 2008 R2 - Failed to send an environment change notification to a log shipping partner node
What do you see here: SELECT * FROM msdb.dbo.log_shipping_primary_secondaries;? If you're not using log shipping for anything, feel free to delete any rows found there. Something is telling SQL Server that this database is involved with log shipping. Also, what is the default database for this specific login?
5h
comment how can I track down the source of “Internal connection fatal error”?
Are you running this in SSMS on your desktop? Where is the server you are running against?
5h
revised What is a non-clustered index scan
added 158 characters in body
5h
comment What is a non-clustered index scan
@Pavel sure, I wasn't saying "This is the ONLY case where a non-clustered index scan would be chosen." Especially given the follow-up comment by the OP. Anyway, I'd argue your scenario still fits into #1 - cost is absolutely considered. You're getting an aggregate that could be satisfied by an NC or CIX scan; SQL Server is going to choose the cheaper one. If they're the same, it's a coin toss.
1d
comment A Script to insert dummy Data in all tables of Database
@GeoffDawdy Not off-hand, no. I could search for one, but, well, so could anybody. :-) Not sure what Microsoft did with that page.
1d
revised A Script to insert dummy Data in all tables of Database
deleted 108 characters in body
1d
answered How To Ignore SQL Update Notification
1d
comment How To Ignore SQL Update Notification
@CapeCodeGunny and does it have client tools, other services, etc.? Humor us and kick off the SP2 installer anyway, and see what components it says do need to be updated.
1d
answered Copy related records from 3 tables to another 3 tables and maintain the relationship between them