I'm not going to explain the basics of partitioning here, so you may want to read the above pages if you're not familiar with it before continuing.
I'm keeping this blog as a repository for SQL tips and tricks I have learned over my time working with SQL Server.
Sunday, 21 April 2013
A Quick Partitioned View Puzzle
I needed to create a few partitions on a legacy SQL Server 2005 Standard Edition instance. As partitions are not supported on standard edition, I needed to go the partitioned index route. In my research into how to implement them, I found some really good resources:
Tuesday, 16 April 2013
ISNULL vs. COALESCE
A friend of mine just asked me when COALESCE should be used. As I'm a DBA and don't do too much SQL development work, I never had a need to use COALESCE (or I never thought I had a need). Therefore, I was inspired to do a little digging. As there has been much written about the similarities and differences I will use this post as a jumping off point to a few good resources that shed light on the topic. Though, I'll highlight some of the points here as well.
Thursday, 4 April 2013
Last SQL Server Instance Start Date
I was interested in finding when a new SQL Server instance I started working on was last restarted and came across this blog post.
Although the information was very good I felt the queries could be tweaked slightly and broken out into the variations needed for the different SQL Server editions.
I find that it is helpful to know when the database server was started due to the various Dynamic Management Views and Functions (DMV) that I use. Many of these collate cumulative statistics beginning at a SQL Server start. Which means that they are cleared down when the instance is restarted, so it's helpful to know the uptime of the server in order to know how much data has been collected and whether it is representative of how the server is being used: i.e. one day's worth of statistics vs. one month's.
As the blog post mentioned above outlines, there are several ways of getting to this data and some of them work only in certain versions of SQL Server. Here they are in my modified form:
NB: I am ignoring SQL Server 2000
Method 1 - Check TempDB create date
SQL Server 2005+
SQL Server 2008+
SQL Server 2005 - 2008
In SQL Server 2012 the extended stored procedure accepts only two parameters
Although the information was very good I felt the queries could be tweaked slightly and broken out into the variations needed for the different SQL Server editions.
I find that it is helpful to know when the database server was started due to the various Dynamic Management Views and Functions (DMV) that I use. Many of these collate cumulative statistics beginning at a SQL Server start. Which means that they are cleared down when the instance is restarted, so it's helpful to know the uptime of the server in order to know how much data has been collected and whether it is representative of how the server is being used: i.e. one day's worth of statistics vs. one month's.
As the blog post mentioned above outlines, there are several ways of getting to this data and some of them work only in certain versions of SQL Server. Here they are in my modified form:
NB: I am ignoring SQL Server 2000
Method 1 - Check TempDB create date
SQL Server 2005+
Method 2 - Check the system DMVSELECT [crdate] AS [LastStartupDate],DATEDIFF(dd,[crdate],GETDATE()) AS [Days since server start]FROM [dbo].[sysdatabases]WHERE [name] = 'tempdb'AND [crdate] < GETDATE() - 0 --filter results to instances restarted greater than X days
SQL Server 2008+
Method 3 - Interrogate the SQL Server error logSELECT [sqlserver_start_time] AS [LastStartupDate],DATEDIFF(dd, [sqlserver_start_time],GETDATE()) AS [Days since server start]FROM [sys].[dm_os_sys_info]
SQL Server 2005 - 2008
SQL Server 2008 - 2012DECLARE @LogNum TINYINTSET @LogNum = 0 --0=current 1=.1 etcDECLARE @LogType TINYINTSET @LogType = 1 --1=SQL 2=AgentDECLARE @ProcessType VARCHAR(64)SET @ProcessType = 'Server'DECLARE @TextSearch VARCHAR(20)SET @TextSearch = 'Server process ID is'DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))INSERT INTO @ErrLogEXEC sys.xp_readerrorlog @LogNum, @LogType , @ProcessType, @TextSearch--grab the first occurrence and report back the timestamp as the last startupSELECT MIN(LogDate) AS [LastStartupDate],DATEDIFF(dd, MIN(LogDate),GETDATE()) AS [Days since server start]FROM @ErrLogWHERE LogDate < GETDATE() - 0 --filter results to instances restarted greater than X days
In SQL Server 2012 the extended stored procedure accepts only two parameters
DECLARE @LogNum TINYINT = 0 --0=current 1=.1 etcDECLARE @LogType TINYINT = 1 --1=SQL 2=AgentDECLARE @ProcessType VARCHAR(64) = 'Server'DECLARE @TextSearch VARCHAR(20) = 'Server process ID is'DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))INSERT INTO @ErrLogEXEC sys.xp_readerrorlog @LogNum, @LogType --, @ProcessType, @TextSearch--grab the first occurrence and report back the timestamp as the last startupSELECT MIN(LogDate) AS [LastStartupDate],DATEDIFF(dd, MIN(LogDate),GETDATE()) AS [Days since server start]FROM @ErrLogWHERE [ProcessInfo] = @ProcessType AND [TEXT] LIKE '%' + @TextSearch + '%'AND LogDate < GETDATE() - 0 --filter results to instances restarted greater than X days
Subscribe to:
Posts (Atom)