Reputation
3,002
Top tag
Next privilege 5,000 Rep.
Approve tag wiki edits
Badges
1 9 34
Impact
~109k people reached

May
26
comment How to archive a whole database?
@Greg I can assure you that space usage is an issue (I assume you mean space by "performance"). I will store multiple copies of the archived data. This is required to prevent bit rot, no matter which of the methods given above I use.
May
26
awarded  Popular Question
May
24
comment How to archive a whole database?
@AaronBertrand the native backup compression is a super fast algorithm with small window. It is significantly worse than the state-of-the-art 7-Zip. 2x over native compression is typical. (The speed is like 50x worse).
May
24
reviewed Approve How to archive a whole database?
May
24
asked How to archive a whole database?
May
23
comment LDF files vs. TRN files vs. Tempdb.MDF+LDF files.:
"all on the same array" Then nothing what you do impacts IO performance. It matters little at what physical offset an IO is issued. What matters is what physical drive it eventually hits.
May
23
comment Symmetric difference operation in Transact-SQL?
@ypercube that does 3 joins or join-like operations. I cannot think of any reason this might be more efficient than a full join.
May
22
comment Split database to fit on SSDs
Use software RAID. Every OS has it built-in. It'd certainly more real-world tested than most hardware RAID software (which sucks).
May
22
comment Is it possible to create a database called C that is a view into all tables in database A + B?
@MartinSmith to add some color to that: SQL Server can't maintain indexed view consistency across databases because they could be restored, copied and they are not crash consistent as a unit.
May
22
comment Shared connection pool in SQL Server and prevention of cross-database queries
Can you use EXECUTE AS to switch to some user (msdn.microsoft.com/en-us/library/ms181362.aspx)? Just an idea, I know nothing about it.
May
22
comment Why is it truncate table, and not simply truncate?
It's probably because SQL is supposed to look like text. On the other hand I'd like to have a TRUNCATE PARTITION/FILEGROUP/DATABASE feature. It's an extensibility point for the language.
May
22
comment Calculate Num_Index_Pages for sizing design
The index levels usually take up a tiny fraction of the table size. You can just leave them out or add 1% or so.
May
21
answered Why is vertical scaling expensive?
May
21
comment Sudden poor SELECT performance on a large table with existing indexes
That only goes to show how rarely I look at IO stats...
May
21
comment Sudden poor SELECT performance on a large table with existing indexes
Maybe you're right. But what does "Scan count 1" mean?
May
20
comment Sudden poor SELECT performance on a large table with existing indexes
"problem ... disk" that's one way to look at it (+1). Or, add an index to avoid the scan on log_events.
May
18
comment SQL Server recreating plans each day
The real problem is that your query plans are so fragile. Recompilations can happen at any time, even during the day. No guarantees. Fix your queries so that the plans become stable. OPTION RECOMPILE or OPTIMIZE FOR UNKNOWN are sledgehammer approaches that can be appropriate and be a quick fix.
May
15
comment Collation by row, not by column
@MisterMagoo that is amazing! I did not know that. At first I suspected that collation simply affected the behavior of casts and ascii as well but I ran a test on that. I converted back to LATIN1_GENERAL_CI_AS and the value did not change back. That's proof you're right.
May
13
comment Collation by row, not by column
As I understand it there is no collation at rest. It's the same bytes. Collation is simply a runtime interpretation. Collation casts do nothing computational.
May
13
comment Collation by row, not by column
No need to store strings in different collations - they would be the same. Collation does not affect the value of strings. It is merely an input parameter for string processing functions. I can see the usefulness of computed columns, though.