Visual Studio 2012–Where’s my Business Intelligence

For those that have been using Visual Studio 2010 to do BI, you will realize by now that Visual Studio does have come with any of the Business Intelligence projects

Just off the oven, you can now goto http://www.microsoft.com/en-us/download/details.aspx?id=36843 to download “Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012”

Once downloaded and installed, you will now be able to see Business Intelligence projects inside Visual Studio 2012

Monitoring SQL Server Performance

Here are the perfmon counters that you can use to monitor SQL Server Performance

Purpose is taken from technet: http://technet.microsoft.com/en-us/library/ms189628%28v=sql.105%29.aspx, and http://msdn.microsoft.com/en-us/library/ms804035.aspx

Perfmon	Purpose	Recommended Values
Process : % Processor Time : sqlservr.exe	Monitors the CPU utilization of SQL Server	Different people have different values, typically anything below 50% is good
SQL Server : SQL Statistics : SQL Compilation/sec	Monitors the number of SQL Compilations per second	<2/sec is good 2-20/sec: not so good 20-100: bad >100: please look into it
SQL Server : SQL Statistics : SQL Re-Compilation/sec	Monitors the number of SQL re-compilations per second	Same as SQL Compilation
SQL Server : Buffer Manager : Buffer Cache Hit Ratio	Percentage of pages found in the buffer cache without having to read from disk.	anything < 99.5%, please look into it
SQL Server : Buffer Manager : Checkpoint Pages/sec	Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.	It varies from system to system, you might want to find your own base line by monitoring this value
SQL Server : Buffer Manager : Page life expectancy	Number of seconds a page will stay in the buffer pool without references.	Preferably at least 300
SQL Server : Buffer Manager : Lazy writes/sec	Number of buffers written per second by the buffer manager’s lazy writer.	As close to 0 as possible
Physical Disk : % Disk Time	Reports the percentage of time that the selected disk drive is busy servicing read or write requests.	0.2 to 0.3 is fast 0.3 to 0.4 is normal > 0.4 is not good
Physical Disk : % Idle Time	Reports the percentage of time that the disk system was not processing requests and no work was queued	< 99.5 is not good
Physical Disk : Avg Disk Read Queue Length	Shows the average number of read requests that were queued for the selected disk during the sample interval.	<2 is recommended
Physical Disk : Avg Disk Sec/Read	Shows the average time, in seconds, of a read of data from the disk.	0.2 to 0.3 is fast 0.3 to 0.4 is normal > 0.4 is not good
Physical Disk : Avg Disk Sec/Write	Shows the average time, in seconds, of a write of data to the disk.	0.2 to 0.3 is fast 0.3 to 0.4 is normal > 0.4 is not good
Physical Disk : Avg Disk Write Queue Length	Shows the average number of write requests that were queued for the selected disk during the sample interval.	<2 is recommended

SQL Server 2008 – Filtered Index

Granted that SQL 2012 is already out, there is actually a feature inside SQL 2008 which is especially useful for some form of slice and dice when you have too much data.

 

This feature is called Filtered Index. You can find more information about it at http://technet.microsoft.com/en-us/library/cc280372.aspx.

 

Generally the purpose is to improve query performance by storing indexes only for a subset of the data. Additionally it reduces costs and storage incurred with index maintenance.

 

The syntax to create one is as follows

create unique nonclustered index [IndexName] on dbo.[TableName]([Fields])
where [Conditions]

Windows SQL Azure Firewall

 

For those who are new to SQL Azure, there are firewall settings that you need to take note of when you first create a SQL Database.

 

By default the firewall is set to block ALL external access

 

So if you wish to enable ALL access (aka disable the firewall), you can use the following settings

SQL Server having high RESOURCE_SEMAPHORE_QUERY_COMPILE waits

If your SQL server is experiencing not so high CPU, but queries are getting timed out, it may be worthwhile to look at activity monitor resource waits (compilation), or run the following SQL

select * from sys.dm_os_wait_stats
where wait_type like ‘resource%’
order by wait_type

If the wait time very high (e.g the first in the list of resource waits), you might want to look at recent queries and see what’s being executed (Activity Monitor -> Processes, right click then details)

Chances are you will see quite a fair number of queries which looks more or less the same.

One easy way to solve this issue is to run dbcc freeproccache, which should effective drop your cpu to normal values once the query is completed. However in the long run, its best to look at the query causing this and see how to make it better

The usual cause of this is when there is alot of SQL which looks more or less the same being executed at the same time. But because the parameters are hard coded, it causes SQL Server to create an execution plan for each and every query.

To solve this, you can either create plan guides, or else find ways to parameterize your queries so that it does not have so many recompiles

 

SQL Server Database Mirroring

SQL Server database mirroring is cool, especially in the areas of cost savings. However setting up a proper database mirroring is not as straightforward as it seems, although it is much much easier than doing a clustering.

Things you need to do

1. Connection String: The databases are in 2 different instances, make sure you use the latest SQL Client with the failover partner parameter to ease the failover
   “Provider=SQLNCLI;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;”
2. SQL Accounts: Mirror the login accounts too!
   Microsoft has a KB (http://support.microsoft.com/kb/918992) which teaches you how to create a stored procedure which prints out a create user sql which you can run on the backup server to create an exact duplicate of the user account in the primary server.
   i’ve attached the script here for easy reference

   USE master
   GO
   IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
   DROP PROCEDURE sp_hexadecimal
   GO
   CREATE PROCEDURE sp_hexadecimal
   @binvalue varbinary(256),
   @hexvalue varchar (514) OUTPUT
   AS
   DECLARE @charvalue varchar (514)
   DECLARE @i int
   DECLARE @length int
   DECLARE @hexstring char(16)
   SELECT @charvalue = ’0x’
   SELECT @i = 1
   SELECT @length = DATALENGTH (@binvalue)
   SELECT @hexstring = ’0123456789ABCDEF’
   WHILE (@i <= @length)
   BEGIN
   DECLARE @tempint int
   DECLARE @firstint int
   DECLARE @secondint int
   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
   SELECT @firstint = FLOOR(@tempint/16)
   SELECT @secondint = @tempint – (@firstint*16)
   SELECT @charvalue = @charvalue +
   SUBSTRING(@hexstring, @firstint+1, 1) +
   SUBSTRING(@hexstring, @secondint+1, 1)
   SELECT @i = @i + 1
   ENDSELECT @hexvalue = @charvalue
   GO

   IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
   DROP PROCEDURE sp_help_revlogin
   GO
   CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
   DECLARE @name sysname
   DECLARE @type varchar (1)
   DECLARE @hasaccess int
   DECLARE @denylogin int
   DECLARE @is_disabled int
   DECLARE @PWD_varbinary varbinary (256)
   DECLARE @PWD_string varchar (514)
   DECLARE @SID_varbinary varbinary (85)
   DECLARE @SID_string varchar (514)
   DECLARE @tmpstr varchar (1024)
   DECLARE @is_policy_checked varchar (3)
   DECLARE @is_expiration_checked varchar (3)

   DECLARE @defaultdb sysname

   IF (@login_name IS NULL)
   DECLARE login_curs CURSOR FOR

   SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
   sys.server_principals p LEFT JOIN sys.syslogins l
   ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
   ELSE
   DECLARE login_curs CURSOR FOR

   SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
   sys.server_principals p LEFT JOIN sys.syslogins l
   ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
   OPEN login_curs

   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   IF (@@fetch_status = -1)
   BEGIN
   PRINT ‘No login(s) found.’
   CLOSE login_curs
   DEALLOCATE login_curs
   RETURN -1
   END
   SET @tmpstr = ‘/* sp_help_revlogin script ‘
   PRINT @tmpstr
   SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
   PRINT @tmpstr
   PRINT ”
   WHILE (@@fetch_status <> -1)
   BEGIN
   IF (@@fetch_status <> -2)
   BEGIN
   PRINT ”
   SET @tmpstr = ‘– Login: ‘ + @name
   PRINT @tmpstr
   IF (@type IN ( ‘G’, ‘U’))
   BEGIN — NT authenticated account/group

   SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']‘
   END
   ELSE BEGIN — SQL Server authentication
   – obtain password and sid
   SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
   EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
   EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

   – obtain password policy state
   SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
   SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name

   SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [' + @defaultdb + ']‘

   IF ( @is_policy_checked IS NOT NULL )
   BEGIN
   SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
   END
   IF ( @is_expiration_checked IS NOT NULL )
   BEGIN
   SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
   END
   END
   IF (@denylogin = 1)
   BEGIN — login is denied access
   SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
   END
   ELSE IF (@hasaccess = 0)
   BEGIN — login exists but does not have access
   SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
   END
   IF (@is_disabled = 1)
   BEGIN — login is disabled
   SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
   END
   PRINT @tmpstr
   END

   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
   CLOSE login_curs
   DEALLOCATE login_curs
   RETURN 0
   GO

3. Preparing mirror server: Make sure the DB is not in use!
   DB Mirroring requires you to restore a backup of the existing db on the mirror side!
4. Preparing mirror DB: When restoring to the mirror site, set it to RESTORE WITH NORECOVERY
5. Setup Mirroring: Pretty straightforward here, right click the db, click properties -> Mirror then click the “Configure Security” button to start the wizard
6. Backup Strategy: Because database mirroring sets the mirror db to be in a perpentual Restoring state, your normal backup jobs won’t work on it. Note this is fixed in SQL Server 2008 maintenance plan, you just need to tick the ignore if not online check box
   Here’s a sample backup script to get you going

   use [master]
   go
   create procedure DailyBackup as
   begin
   declare @BackupPath varchar(300)
   declare @DBName varchar(256)
   declare @RecoveryModel int
   declare @DT varchar(100)
   declare @SQL varchar(4000)

   set @BackupPath=N’D:\backup\’

   DECLARE db_cursor CURSOR FOR
   select [name], [recovery_model]
   from sys.databases
   where state=0 and name not in (‘tempdb’,'model’)
   OPEN db_cursor
   FETCH NEXT FROM db_cursor INTO @DBName, @RecoveryModel

   WHILE @@FETCH_STATUS = 0
   BEGIN
   if (@RecoveryModel <> 3)
   begin
   – Backup Transaction Log
   set @DT = CONVERT(CHAR(8), GETDATE(), 112) + ‘_’ + replace(CONVERT(CHAR(8), GETDATE(), 108), ‘:’, ‘_’)
   set @SQL = N’BACKUP LOG [' + @DBName + N'] TO DISK = N”’ + @BackupPath + @DBName + @DT + N’.trn” WITH NOFORMAT, INIT, NAME = N”’ + @DBName + N’ Transaction Log Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10′
   print (@SQL)
   exec (@SQL)
   end

   –Backup Database
   set @DT = CONVERT(CHAR(8), GETDATE(), 112) + ‘_’ + replace(CONVERT(CHAR(8), GETDATE(), 108), ‘:’, ‘_’)
   set @SQL = N’BACKUP DATABASE [' + @DBName + N'] TO DISK = N”’ + @BackupPath + @DBName + @DT + N’.bak” WITH NOFORMAT, INIT, NAME = N”’ + @DBName + ‘ Full Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10′
   print (@SQL)
   exec (@SQL)

   FETCH NEXT FROM db_cursor INTO @DBName, @RecoveryModel
   END

   CLOSE db_cursor
   DEALLOCATE db_cursor
   end