Archive

Archive for the ‘SQL Server’ Category

Visual Studio 2012–Where’s my Business Intelligence

March 18, 2013 Leave a comment

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

image

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

image

Advertisements
Categories: ASP.NET, SQL Server

Monitoring SQL Server Performance

July 4, 2012 Leave a comment

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
Categories: SQL Server

SQL Server 2008 – Filtered Index

May 31, 2012 Leave a comment

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]

Categories: SQL Server

Windows SQL Azure Firewall

December 15, 2011 2 comments

 

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

image

Categories: Azure, SQL Server

SQL Server having high RESOURCE_SEMAPHORE_QUERY_COMPILE waits

February 16, 2011 Leave a comment

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

 

Categories: Performance, SQL Server

SQL Server Database Mirroring

August 20, 2009 Leave a comment

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

Categories: SQL Server