A DBA's Journal

SQLIO - Testing, Round 1

2011-12-05T13:30:00.001-08:00

For the past couple weeks I've been doing some analysis on the database environment I'm administering. I figured that is a great place to get my feet wet at my new position. It forces me to really dive in and understand the storage subsystem, which for me is an EMC CX3-20 SAN. The other significant components include the QLogic QLA2460 HBAs and the Brocade 5000 switches. All-in-all the environment seems more than sufficient to support a Microsoft Dynamics AX implementation.

My research was two-fold: first, I wanted to understand the theoretical limits of each component. Then I used SQLIO - which isn't really a SQL tool at all - to gather some actual metrics. I've found that you can't really just line up your expectations with the results because of some rather obscure, yet significant items (cache, for example).

I proceeded to document everything I could, often learning and researching along the way:

First, I started by examining the partition offset and file allocation unit size for each logical disk. Apparently this used to be a little more cumbersome pre-W2K8, but I'm on W2K8 R2 so the partition offset was 65,536 bytes (64KB) across the aboard. I retrieved this by doing to a command prompt and typing issuing the following:

wmic partition get BlockSize, StartingOffset, Name, Index

To get the file allocation unit size for each disk, I issue the following command(s):

fsutil fsinfo ntfsinfo <drive:>

In most cases this value should be 65,536 bytes (64K) for partitions on which SQL Server resides.

The HBAs have what is called an "execution throttle", which by definition "signifies the maximum number of outstanding commands that can execute on any one HBA port". Each of my nodes has two HBA adapters - some had an execution throttle of 16 while others were at 256. I standardized them all by going with the upper limit and will adjust accordingly. What I'm trying to accomplish is to develop a baseline from which to compare future results in the event I reconfigure anything. Also, according to QLogic these HBAs can handle 150,000 IOPS and roughly 750MB/sec throughput in full duplex.

Next, I determined that all devices were communicating at 4Gbps. The switches have a maximum aggregate of 256Gbps end-to-end.

It's also important to understand queue depth per port on the SAN. The maximum IOPS that can be handled on a given port at a given time is 2048 for EMC arrays. That's the maximum, however. What we can do is calculate the queue depth for each LUN using the following formula (in my case anyway): (#data disks x 14) + 32. So in a RAID 10, 10 disk array you will have a queue depth of (5 x 14) + 32, or 108. Ultimately we want to ensure no single LUN receives more parallel requests than it can handle. That also goes for the port, so once you have calculated the queue depth for each LUN, sum them up to determine the total theoretical queue depth you could support, although very unlikely it would ever happen.

This is going to be a work in-progress, but if you have any feedback or suggestions, this is relatively new territory for me as a DBA. It's quite interesting though!

Blogging in 2012

2011-12-01T13:04:00.001-08:00

I must admit I haven't blogged nearly as much as I had hoped I would, but 2012 is going to be a year where I really focus on getting my thoughts, discoveries and useful tips into my blog. I look forward to creating a bit of a community to share ideas and experiences.

Grant Full Control in SharePoint 2010

2011-12-01T12:58:00.001-08:00

To grant a user or group administrator access to a given web application do as follows:

1) Open SharePoint Central Administration.
2) Click on Application Management in the left menu.
3) Under Web Applications in content area, click Manage web applications.
4) Highlight the Name of the web application in the content area.
5) In the ribbon, click User Policy - this will launch the Policy for Web Application window.
6) Click the Add Users button, add the user and click OK.

That should do it!

Identify Unused Indexes

2011-06-09T04:35:00.000-07:00

Unused indexes have all sorts of negative affects on your database environment, from disk space consumption to performance related issues. As queries and stored procedures change over time, so may the usage of indexes, often times to the point where they are only maintained (inserts/updates) but hardly read from. Identifying these indexes can be done using the following script - you can adjust it to suit your needs.



-- Possible bad Indexes (writes > reads)

DECLARE @dbid INT

SELECT  @dbid = DB_ID()



SELECT  'Drop Statement' = 'DROP INDEX ' + i.name + ' ON [dbo].'

        + OBJECT_NAME(s.object_id)

       ,CASE user_seeks + user_scans + user_lookups

          WHEN 0 THEN 100

          ELSE (ROUND(((user_updates)

                       / CAST((user_updates + user_seeks + user_scans

                               + user_lookups) AS FLOAT)),4)) * 100

        END AS '% Writes'

       ,'Table Name' = OBJECT_NAME(s.object_id)

       ,'Index Name' = i.name

       ,i.fill_factor

       ,'Total Writes' = user_updates

       ,'Total Reads' = user_seeks + user_scans + user_lookups

       ,'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM    sys.dm_db_index_usage_stats AS s

        INNER JOIN sys.indexes AS i ON s.object_id = i.object_id

                                       AND i.index_id = s.index_id

WHERE   OBJECTPROPERTY(s.object_id,'IsUserTable') = 1

        AND s.database_id = @dbid

        AND user_updates > (user_seeks + user_

scans + user_lookups)

        AND i.index_id NOT IN (0,1)

ORDER BY '% Writes' DESC

       ,'Total Writes' DESC

       ,'Total Reads' ASC

Creating a SQL Server Linked Server to MySQL

2011-01-28T10:07:00.000-08:00

If you get the following error after setting up a SQL Server Linked Server to a MySQL database:

mysql linked server error 7303 is not allowed to connect

First, take note of the IP address of the server that the linked server was created on. Then use MySQL Workbench to connect to the MySQL instance using SSH and run the following command:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON database.*

TO user@ipaddress

IDENTIFIED BY 'password';

That's it!

Creating a Full-Text Index using Indexed View

2010-06-02T07:27:00.000-07:00

Since this isn't something I set up every day, I wanted to journal the process of creating a full text index using an indexed view.

First, you have to create your view, ensuring that there is a unique identifier - that is a prerequisite for creating an indexed view.

Secondly, you have to create the index using the following syntax:
CREATE UNIQUE CLUSTERED INDEX IX_ViewName_ColumnName ON ViewName (ColumnName)

Now that you have an indexed view, you can go ahead and create the full text index using that view.

New Data Types in SQL Server 2008

2010-02-12T20:30:00.001-08:00

http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-the-new-data-types/

Great post for reference.

Selecting a random sample

2010-02-12T14:08:00.000-08:00

If you are interested in querying for a random X number of records, or TOP Y percent of records, you can use the following syntax:

SELECT TOP 5

FROM YourTable

ORDER BY NEWID()

SELECT TOP 50 PERCENT
*
FROM YourTable
ORDER BY NEWID()

Random Number Generator

2010-02-12T09:43:00.000-08:00

---- Create the variables for the random number generation
DECLARE @Random INT ;
DECLARE @Upper INT ;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1
---- The lowest random number
SET @Upper = 999
---- The highest random number
SELECT @Random = ROUND(( ( @Upper - @Lower ) * RAND() + @Lower ), 0)
SELECT @Random

Checking if a table exists before dropping it

2010-02-11T09:43:00.000-08:00

I often see folks create "temporary" tables that are not true #temp tables - with the intent of refreshing the entire table's contents. This could be for data warehousing purposes or to simply have a table of aggregated data in your OLTP system for reporting purposes. To ensure the table is not there before you execute your SELECT INTO statement, use the following syntax:

IF OBJECT_ID('YourTempTable') IS NOT NULL

DROP TABLE YourTempTable

-- then populate the table with your data

SELECT * INTO YourTempTable FROM...

Pretty straight forward.

Replication, Restores, and Reseeding Identity Columns

2009-11-03T12:12:00.000-08:00

I recently ran into an issue where, after restoring a replicated database, all the identity columns were seeded to start over at 1. To make sense of my situation, I'll describe it a bit first.

Situation
I have database servers in multiple geographic locations: our production server is off-site while our development environment is on-site. Being a smaller company, our resources to get high-speed lines into our office are limited and not real high priority right now. I do, however, have a replication subscriber on-site that subscribes to our production database - works great.

Problem
In order to have a fresh testing environment, I wanted to restore our production database into our development environment each morning so developers had fresh live data to work against. Problem is, to copy a production backup from our remote location on-site would take a half day whilst consuming most of our bandwidth.

Solution
I decided to run a backup of our on-site replicated database and restore directly from that backup. Works great - takes 15 minutes or so. However, as described earlier, the identity columns are not seeded properly - developers were getting primary key violation errors because of this.

Here is how you can resolve this issue in a single script, which for me I included as a step in my restore process. Basically I'm reseeding all the user tables that have identity columns.

SET NOCOUNT ON

DECLARE @table_name VARCHAR(200),
    @hasidentity TINYINT

DECLARE table_cursor CURSOR
    FOR SELECT TABLE_NAME
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE'
                AND TABLE_CATALOG = 'database-name'
        ORDER BY TABLES.TABLE_NAME

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name

WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @hasidentity = OBJECTPROPERTY(OBJECT_ID(@table_name),
                                              'TableHasIdentity')
        IF @hasidentity = 1
            BEGIN
                DBCC CHECKIDENT ( @table_name, RESEED )
            END

        FETCH NEXT FROM table_cursor INTO @table_name
    END
CLOSE table_cursor
DEALLOCATE table_cursor

SQL Server: Map Orphaned Users

2009-10-31T17:50:00.000-07:00

I've put together the following stored procedure that will map ''orphaned" users in a database. This often happens when you restore a database into a new environment.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_map_orphaned_db_users]    Script Date: 10/31/2009 19:44:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
* PROCEDURE NAME: sp_map_orphaned_db_users
* DESCRIPTION:
* Maps orphaned database users to server logins of the same name.
*
*/
ALTER PROCEDURE [dbo].[sp_map_orphaned_db_users]
(
@with_report BIT = NULL
,@database NVARCHAR(128) = NULL
,@report_only BIT = NULL /* Pass NULL or 0(zero) to execute and 1 to show actions that would be taken. */
)
AS
BEGIN

    SET NOCOUNT ON

    SELECT
        @report_only = ISNULL(@report_only, 0)
       ,@database = ISNULL(RTRIM(LTRIM(@database)), '')
       ,@with_report = ISNULL(@with_report, 0)

    DECLARE
        @UserName SYSNAME
       ,@dbname SYSNAME
       ,@sql NVARCHAR(4000)
       ,@orphanTableName NVARCHAR(128)

    SELECT
        @orphanTableName = '##orphans_' + REPLACE(CAST(NEWID() AS NVARCHAR(128)), '-', '')

    CREATE TABLE #orphans
    (
     UserName SYSNAME
    ,UserSID VARBINARY(85)
    ,dbname SYSNAME
    )

    SELECT
        @sql = N'
        IF OBJECT_ID(''' + @orphanTableName + ''') IS NOT NULL
        BEGIN
            DROP TABLE ' + @orphanTableName + '
        END
        CREATE TABLE ' + @orphanTableName + '
        (
            UserName SYSNAME,
            UserSID VARBINARY(85),
            dbname SYSNAME
        )'
    EXEC (@sql)

    IF @database = ''
        BEGIN

            SELECT
                @sql = N'
            USE [?]
            CREATE TABLE #report
            (
                UserName SYSNAME,
                UserSID VARBINARY(85)
            )

            INSERT INTO #report
            EXEC sp_change_users_login @Action=''Report''

            ALTER TABLE #report
            ADD dbname SYSNAME NULL

            UPDATE #report
            SET dbname = ''?''

            INSERT INTO ' + @orphanTableName + '
            SELECT
                UserName,
                UserSID,
                dbname
            FROM #report'

            EXEC sp_msforeachdb @sql
        END
    ELSE
        BEGIN

            SELECT
                @sql = '
        USE [' + @database + ']
        CREATE TABLE #report
        (
            UserName SYSNAME,
            UserSID VARBINARY(85)
        )

        INSERT INTO #report
        EXEC sp_change_users_login @Action=''Report''

        ALTER TABLE #report
        ADD dbname SYSNAME NULL

        UPDATE #report
        SET dbname = ''' + @database + '''

        INSERT INTO ' + @orphanTableName + '
        SELECT
            UserName,
            UserSID,
            dbname
        FROM #report'

            EXEC (@sql)

        END

    SELECT
        @sql = N'
        INSERT INTO #orphans
        SELECT
            UserName,
            UserSID,
            dbname
        FROM ' + @orphanTableName

    EXEC (@sql)

    SELECT
        @sql = N'
        IF OBJECT_ID(''' + @orphanTableName + ''') IS NOT NULL
        BEGIN
            DROP TABLE ' + @orphanTableName + '
        END'
    EXEC (@sql)

    DECLARE @userCount INT

    SELECT
        @userCount = COUNT(*)
    FROM
        #orphans o
        INNER JOIN master.sys.syslogins l ON l.[name] = o.UserName

    DECLARE rc CURSOR LOCAL FAST_FORWARD
        FOR SELECT
                o.UserName
               ,o.dbname
            FROM
                #orphans o
                INNER JOIN master.sys.syslogins l ON l.[name] = o.UserName

    OPEN rc

    FETCH NEXT FROM rc INTO @UserName, @dbname

    WHILE @@FETCH_STATUS = 0
        BEGIN

            SELECT
                @sql = 'USE ' + @dbname + ' EXEC sp_change_users_login @Action=''update_one'', @UserNamePattern=''' + @UserName + ''', @LoginName=''' + @UserName + ''''
            IF @report_only = 1
                BEGIN
                    PRINT 'WOULD HAVE ASSOCIATED USER [' + @UserName + '] IN DATABASE [' + @dbname + '] WITH SERVER LOGIN [' + @UserName + ']'
                END
            ELSE
                BEGIN
                    EXEC (@sql)
                END

            IF @with_report = 1
                AND @report_only = 0
                BEGIN
                    PRINT 'ASSOCIATED USER [' + @UserName + '] IN DATABASE [' + @dbname + '] WITH SERVER LOGIN [' + @UserName + ']'
                END

            FETCH NEXT FROM rc INTO @UserName, @dbname

        END

    IF (@userCount > 0)
        BEGIN
            PRINT CAST(@userCount AS NVARCHAR(50)) + N' USERS PROCESSED.'
        END
    ELSE
        BEGIN
            PRINT 'NO ORPHANED USERS TO PROCESS.'
        END

    CLOSE rc
    DEALLOCATE rc
END

How to move an object from one schema to another

2009-10-30T07:46:00.000-07:00

There are various reasons you may wish to move an object to a different schema. The reason may be for security purposes or simply for logical grouping. No matter the reason, the syntax is rather simple:

ALTER SCHEMA newschema TRANSFER oldschema.objectname

Took me a bit to find this piece of information, so I found it a worthy post!

Stored Procedure: Dynamic IN Clause Solution

2009-10-29T08:21:00.000-07:00

I'm sure many database programmers have run across the issue of wanting to pass a delimited string of values as a parameter, then using that in your IN clause. Example:

CREATE PROCEDURE MyProc @StringOfValues VARCHAR(200)

AS

SELECT *

FROM table

WHERE column IN (@StringOfValues)

To execute, you do the following:
EXEC MyProc '1,2,3'

If the column data type is an integer, you will likely get the error "Conversion failed when converting the varchar value '1,2,3' to data type int." Now, you could certainly create a string for the SQL statement - which is a pain to say the least - then EXEC(thestring), but let's look at a nice alternative to that.

First, you will need a table valued function that allows you to pass in a delimited list of values:

CREATE FUNCTION [dbo].[ParamParser]
    (
     @delimString VARCHAR(255)
    ,@delim CHAR(1)
    )
RETURNS @paramtable TABLE ( id INT )
AS BEGIN
    DECLARE @len INT
       ,@index INT
       ,@nextindex INT

    SET @len = DATALENGTH(@delimString)
    SET @index = 0
    SET @nextindex = 0

    WHILE ( @len >= @index )
        BEGIN

            SET @nextindex = CHARINDEX(@delim, @delimString, @index)

            IF ( @nextindex = 0 )
                SET @nextindex = @len + 2

            INSERT @paramtable
                    SELECT SUBSTRING(@delimString, @index, @nextindex - @index)

            SET @index = @nextindex + 1

        END
    RETURN
   END

Now that you have that table valued function, you can rewrite your stored procedure as such:

CREATE PROCEDURE MyProc @StringOfValues VARCHAR(200)

AS

SELECT *

FROM table

INNER JOIN dbo.ParamParser(@StringOfValues,',') tvf ON table.column = tvf.id

When you then execute your stored procedure call, you get back the results as expected - filtered accordingly on your delimited string of values.

SQL 2008 Replication - Convert from TEXT to VARCHAR(MAX)

2009-10-27T10:44:00.001-07:00

SQL Server Replication has evidently come a long way since I began using it. Back in the day it seemed like every action was equivalent to a visit to the dentist, but today after hours of searching and digging, I found the solution to my problem was actually quite simple.

The task at hand was that that I had three columns that I wanted to convert from TEXT data types to VARCHAR(MAX).

First I tried the obvious: simply changing the data type via the SSMS GUI, but received the following error "Unable to modify table. Cannot drop the table 'tablename' because it is being used for replication." Strike one.

Next, I tried to script out my changes, but SSMS gave me a pretty awful script that basically created a temporary table, migrated the data, dropped the original, and renamed the temporary table back. That just didn't sound like a sound plan in a replicated environment.

Then I started investigating the replication stored procedures to see if there was one that allowed me to change the properties of an article (table in this case), to no avail.

Continuing on, I considered removing the article from the publication, modifying the column, then adding it back into the publication. Now, while I figured this method would work, I wanted to try one last thing: I did recall reading that running the raw ALTER TABLE ALTER COLUMN command may work, and having not tried that, I gave it a shot.

I opened a new query window and ran the following:
ALTER TABLE tablename ALTER COLUMN columnname VARCHAR(MAX)

BINGO! I checked my subscribers and within seconds they too inherited the schema change (which BTW is an option you can select on the publication properties).

Too easy... but had to share. Hopefully it sames someone some time down the road.