Wednesday, August 17, 2011

Buffer count and MaxTransferSize for backups in sql server

Here is the procedure to get the Buffer count and MaxTransfersize for your backups in sql servers

use the following trace flags:
DBCC TRACEON (3605)
DBCC TRACEON (3213)

Then run your backups and you can see the following information in your errorlog.

Backup/Restore buffer configuration parameters
Memory limit: 6143MB
Buffer count:               6
Max transfer size:          64 KB
Min MaxTransfer size:       64 KB
Total buffer space:         0 MB
Buffers per read stream:    6
Buffers per write stream:   6
Tabular data device count:  1
FileTree data device count: 0
Filesystem i/o alignment:   512

Monday, August 1, 2011

Estimate Compression in sql server 2008

Another good feature in 2008.
We can estimate the compression before actually executing it against the tables.
Here is the stored proc:

sp_estimate_data_compression_savings
or
sp_estimate_data_compression_savings <schemaname>,<tablename>,null,null,'page'

null,null are index id and partition numbers. you can give the specific index ids or partition no.s if you know otherwise null will list down for all partitions and all indexes on that table.
<schemaname>,<tablename>,null,null,'row'

Filtered indexes in sql server 2008

Just read a blog of Amit Bansal about Filtered indexes.
This is a really good feature.
You can create indexes on only a set of data like this:

create index on table(col1) where col1>234

Using this we can actually filter out the old data which is not used frequently and create index only on the current live data.

You can check the article here:
http://technet.microsoft.com/en-us/library/cc280372.aspx