[Tips] HOW TO find page splits by reading the transaction log

http://strictlysql.blogspot.com/2009/10/identifying-page-splits.html

There are two tricks in here. The first is using ::fn_dblog (undocumented function) to read the transaction log for a database.
The second is pulling out the delete messages involved in a page split and using that to determine where the splits are occurring.

select AllocUnitName, count([AllocUnitName]) [Splits]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
    and parsename(AllocUnitName,3) <> 'sys'
group by AllocUnitName

[Tuning] Getting only the DATE from a datetime

Many ways to handle dates, and most time I don't think about it - then came across a gigantic thread over two years that discussed it.


Comparing dates. Traditionally, for simplicity, to mark particular dates, I've used convert(char(8),a_date_field,12), but it’s non-SARGable, and not terribly efficient.


Looking online I found a couple alternatives, though they’re mostly useful when having to deal with large tables.

http://blog.sqlauthority.com/2008/10/18/sql-server-retrieve-select-only-date-part-from-datetime-best-practice-part-2/

For SQL Server 2008 it's pretty simple:

SELECT  cast(GETDATE() as date)
or
SELECT CONVERT(date,GETDATE())

(date is a new datatype that only stores the date, and is 3 bytes in size)

For SQL Server 2005, there seem to be two options. Naturally, neither is SARGable from what I’ve seen.

SELECT CONVERT (datetime, FLOOR(CONVERT (float, a_field_name)))
and
SELECT DATEADD ( DAY, DATEDIFF(DAY, 0, a_field_name), 0)

The first one performs math on the internally-stored FLOAT value of the datetime - right now, for instance, is 40315.37003125, where the whole number is the day and the fraction is the time. (Note that you can’t cheat and use INT; it can round up during the conversion).

The second one does date manipulation - there's a whole article online about using datediff and dateadd and the like to get the first day of the month, the first day of last month, etc, etc.

From what I’ve seen online, the FLOOR option can be slightly faster than the DATEADD, but either is considerably faster than CONVERT(char(8)).

[Tuning] Disk usage, redux

Here's a slightly different version of an earlier query, that tells you how much IO each database is getting.

This one is by database, not by file - did this since it makes dealing with TEMPDB easier. So you can't use it for physical file IO - that's the other version. And it's in GB, since that's our issue.

SELECT

sysdb.name AS name,

master_files.type_desc,

vfs.sample_ms,

SUM(vfs.num_of_bytes_written)/1024/1024/1024 AS GB_written,

SUM(num_of_bytes_read)/1024/1024/1024 AS GB_read,

SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 AS GB_total_IO,

SUM(size_on_disk_bytes)/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files

ON vfs.[file_id] = master_files.[file_id]

AND vfs.database_id = master_files.database_id

INNER JOIN master..sysdatabases sysdb ON vfs.database_id = sysdb.dbid

GROUP BY

sysdb.name, master_files.type_desc,

-- master_files.physical_name,

vfs.sample_ms

ORDER BY SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 desc

[Tuning] Filtered indexes...and filtered statistics.

So, SQL Server (starting with 2008) now has Filtered Indexes. Which are great - since an index is basically just a copy of the data, why copy particular fields for the entire table when you only query particular subsets of data? Add a WHERE clause to your index, and now you're querying subsets of data.


However, while reading up on it I came across this:
http://msdn.microsoft.com/en-us/library/ms190397.aspx
which introduces filtered statistics(!).

Instead of

CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)

which would give you stats for the entire range, do this:

CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3)

which will only compute statistics for that subset of data.

[Files] Tricking Excel with SP_SEND_DBMAIL

My pain = your gain. I needed to automate a process to send a file to an end user. They would just double-click it and have it open in Excel. The problem was due to a field with a leading zero, which Excel will simply lop off. One way around is to create an XML file. I've done that before, but this is simpler and ideal for this situation. The syntax and parameters are important.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'youremailprofile', 
    @recipients = 'dev@null.com', 
    @subject = 'Here is your file pull done',
    @query = 'SELECT a, b, convert(varchar(12),c) as C, char(61) + char(34) + leadingzerossuck + char(34) as leadingzerossuck FROM mytemptable', 
    @attach_query_result_as_file = 1, 
    @query_attachment_filename = 'yourfilename.csv', 
    @query_result_separator = ' ', -- tab
    @exclude_query_output = 1, 
    @append_query_error = 1