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.
I'm keeping this blog as a repository for SQL tips and tricks I have learned over my time working with SQL Server.
Tuesday, 16 April 2013
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
Monday, 18 March 2013
SQL Server 2005+ Performance Tuning - Part 2
In my last post I covered the reasons for poor performance and the ways of pinpointing performance bottlenecks.
In this post I will detail some of the ways of resolving those performance bottlenecks.
Thursday, 14 March 2013
SQL Server 2005+ Performance Tuning - Part 1
Tuesday, 12 March 2013
Remove a Cursor From a Stored Procedure - Part 2
| Why is it so slow?!?!? |
Thursday, 7 March 2013
Remove a Cursor From a Stored Procedure - Part 1
In my organisation, and no doubt many others, the problem stems from the fact that the developers writing SQL are not SQL developers. Rather they are [insert relevant coding language] developers. And in many programming languages processing is best performed on a row-by-row basis. Not so SQL, especially T-SQL.
Sunday, 24 February 2013
Removing a Merge Replication Publication
Merge replication in any SQL Server edition is notoriously bad tempered. It does seem to be getting more stable and "intelligent" as time goes on but problems still rear their ugly heads on occasion.
Monday, 28 January 2013
DMV Starter Pack
A while back I discovered the DMV Starter Pack, a really useful set of queries exploiting the very powerful Dynamic Management Views and Functions in SQL Server 2005 and 2008.
What I've done is add a few bits to the queries I use most often. Mostly the index queries. In the missing and bad indexes queries I added CREATE and DROP statement columns. Obviously, the CREATE statements conform to the naming conventions of my company [IX_tablename_column1name_column2name...], but this is quite easily modified. I also use a fillfactor of 90 by default. I hope this is helpful.
What I've done is add a few bits to the queries I use most often. Mostly the index queries. In the missing and bad indexes queries I added CREATE and DROP statement columns. Obviously, the CREATE statements conform to the naming conventions of my company [IX_tablename_column1name_column2name...], but this is quite easily modified. I also use a fillfactor of 90 by default. I hope this is helpful.
Thursday, 20 December 2012
Data Encryption and Protection - Part 2
In my last post I ran through the basics of SQL Server's database encryption options: Transparent Database Encryption (TDE) and Cell-Level Encryption. In this post I'm going to discuss some of the considerations required before implementing encryption.
There are four main points to consider:
There are four main points to consider:
- What do I need to accomplish
- Cost of implementation
- Cost of rollback/removal
- Performance impact
Wednesday, 12 December 2012
Monitoring out of hours DB access
Recently a client has asked to monitor staff usage of a desktop application out of hours and out of the office. So the specified criteria for tracking db access were:
A while back I posted about Logon Triggers when I first learned about them. I gave the example found in BOL about limiting the number of connections a user can have at any one time. Since then I haven't had an opportunity to put such triggers into practice. So it was, sadly, quite exciting to finally jump into it.
First I created a table in my AdminDB:
I also needed to access the IP address of the incoming connection - to filter out tthe company IP range. So I'm using the EVENTDATA() function. Event data is held as XML and therefore an XML statement was required. For more info on EVENTDATA() see MSDN.
With the above there is now a table to be accessed for usage/auditing reports. It would probably be a good idea to archive and/or cleanup the LogonTracking table just to keep this tidy and efficient.
It would be nice to hear what other people have used logonn triggers for as well.
- Between 17:00 and 09:00 hours on weekdays
- Weekends
- Computers from outside company offices
- Run a job with a query interrogating the sys.dm_exec_sessions dmv checking for sessions within the specified criteria
- Create a logon trigger that fires at each logon event and populates a table with the required data when a logon event occurs within the specified criteria
A while back I posted about Logon Triggers when I first learned about them. I gave the example found in BOL about limiting the number of connections a user can have at any one time. Since then I haven't had an opportunity to put such triggers into practice. So it was, sadly, quite exciting to finally jump into it.
First I created a table in my AdminDB:
USE [AdminDB] CREATE TABLE dbo.tblLogonTrackingThen the trigger itself:
(
stblLogonTrackingID int primary key clustered identity (1,1) NOT NULL,
login_time datetime NOT NULL,
[host_name] varchar(50) NOT NULL,
[program_name] varchar(100) NOT NULL,
[EventData] VARCHAR(15) NULL
) ON [PRIMARY]
USE [master]I've created the trigger 'ON ALL SERVER' since I need to use it for several databases/applications. But it could be created just for a single database by running it on the required db and using 'ON DATABASE'. Due to the nature of the applications I've also included a NOT EXISTS statement so I don't record the same user logging in and out successively. The goal here is to know that someone is accessing the database in out of hours periods, not necessarily how many times they are accessing the system. But that should be relatively easy to modify based on requirements.
ALTER TRIGGER trLogonTracking
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF DATEPART(HH, getdate()) NOT BETWEEN 9 AND 17
OR (DATEPART(DW,GETDATE()) IN (1,7))
BEGIN
INSERT INTO AdminDB.dbo.tblLogonTracking (login_time,[program_name],[host_name],[EventData])
SELECT GETDATE(),APP_NAME(),HOST_NAME(),
EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(15)')
WHERE ORIGINAL_LOGIN() IN ('AppLogin1','AppLogin2')
AND (APP_NAME() LIKE '%app1_name %' OR APP_NAME() LIKE '%app2_name%')
AND NOT EXISTS (SELECT 1 FROM AdminDB.dbo.tblLogonTracking as a
WHERE DATEDIFF(mi,GETDATE(),a.login_time) > 60
AND APP_NAME() = a.[program_name]
AND HOST_NAME() = [host_name])
AND EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(15)') NOT LIKE '10.1.%'
END
END
I also needed to access the IP address of the incoming connection - to filter out tthe company IP range. So I'm using the EVENTDATA() function. Event data is held as XML and therefore an XML statement was required. For more info on EVENTDATA() see MSDN.
With the above there is now a table to be accessed for usage/auditing reports. It would probably be a good idea to archive and/or cleanup the LogonTracking table just to keep this tidy and efficient.
It would be nice to hear what other people have used logonn triggers for as well.
Subscribe to:
Posts (Atom)
