Max INT Identity Value Reached (DBCC CheckIdent)

One of my colleagues shared the following experience. Recently, the identity column on one of his tables reached the maximum value allowable for an INT data type (2,147,483,647). Obviously, this caused all sorts of errors, as the application was unable to insert records into the table. This is a customer-facing table, so the issue needed to be resolved quickly. Converting the column to a BIGINT would require a significant amount of down-time. The solution? Reseed the identity column to -2,147,483,648 and have the values count upward toward zero. This took only seconds and the app was back up immediately. This is, of course, a temporary solution; it allows the team to schedule a more convenient time to resolve the issue. Here’s how to do this: DBCC CHECKIDENT (’tableName’, RESEED, -2147483648); Thanks, Peter, for sharing this tip!

Partitioning Example

The following code will walk you through the process of creating a partitioned table: ——————————– — Create A Partitioned Table — ——————————–   /* Create a partition function. */ Create Partition Function [test_monthlyDateRange_pf] (smalldatetime) As Range Right For Values (’2008-01-01′, ’2008-02-01′, ’2008-03-01′); Go   /* Associate the partition function with a partition scheme. */ Create Partition Scheme test_monthlyDateRange_ps As Partition test_monthlyDateRange_pf All To ([Primary]); Go   /* Create your first partitioned table! Make sure the data types match. */ Create Table dbo.orders ( order_id int Identity(1,1) Not Null , orderDate smalldatetime Not Null   Constraint PK_orders Primary Key Clustered ( orderDate , order_id ) ) On test_monthlyDateRange_ps(orderDate); Go   /* Create some records to play with. */ Insert Into dbo.orders Select ’2007-12-31′ Union All Select ’2008-01-02′ Union All Select ’2008-01-03′ Union All Select ’2008-01-04′ Union All Select ’2008-02-01′ Union All Select ’2008-02-02′ Union All Select ’2008-03-01′ Union All Select ’2008-03-02′;   /* The $partition function can be used to interrogate partition data. Let’s use it to see where those records are physically located. */ Select $partition.test_monthlyDateRange_pf(orderDate) As ‘partition_number’ , * From dbo.orders;   /* By default, all new indexes are created on the partition. Let’s create an aligned index */ … Continue reading

Partitioning 101

A few posts ago, I briefly discussed partitioning. This article will continue that discussion and focus on horizontal partitioning within SQL Server 2005. What is partitioning? As I’ve mentioned before, horizontal partitioning is the physical segregation of a single logical table name into multiple, *identical* physical structures. In SQL 2005, all table and index pages actually exist on a partition. However, if you’ve never created a partitioning scheme, then by default each of your tables and indexes contain just a single partition. I’ll also be talking about aligned indexes and tables, so let’s go ahead and define that here. Two tables sharing the same partitioning function and the same partitioning key are aligned. Similarly, a partitioned index on a partitioned table sharing the same partitioning key is called an aligned index. Having tables and indexes in alignment can reduce CPU and improve parallel plan execution. Why partitioning? The two most common reasons to implement partitioning are performance improvement and ease of archiving. Let’s talk about performance improvement first. We made the decision to implement partitioning in one of our data warehouses because of the size of the tables. We had a couple of tables that exceeded a billion rows, and … Continue reading

Index Interrogation Script

As a continuation of the last post on indexes, here’s a script I wrote for index interrogation.  This stored procedure was the result of repeatedly having to lookup information on included columns. If ObjectProperty(Object_ID(’dbo.dba_indexLookup_sp’), N’IsProcedure’) Is Null Begin Execute (’Create Procedure dbo.dba_indexLookup_sp As Print ”Hello World!”’) RaisError(’Procedure dbo.dba_indexLookup_sp created.’, 10, 1); End; Go   Set ANSI_Nulls On; Set Ansi_Padding On; Set Ansi_Warnings On; Set ArithAbort On; Set Concat_Null_Yields_Null On; Set NoCount On; Set Numeric_RoundAbort Off; Set Quoted_Identifier On; Go   Alter Procedure dbo.dba_indexLookup_sp   /* Declare Parameters */ @tableName varchar(128) = Null As /*********************************************************************** Name: dba_indexLookup_sp   Author: Michelle F. Ufford   Purpose: Retrieves index information for the specified table.   Notes: If the tableName is left null, it will return index information for all tables and indexes.   Called by: DBA   Date User Description ——————————————————————– 2008-10-28 MFU Initial Release ************************************************************************ Exec dbo.dba_indexLookup_sp @tableName = ‘myTableName’; ***********************************************************************/   Set NoCount On; Set XACT_Abort On;   Begin   Declare @objectID int;   If @tableName Is Not Null Set @objectID = Object_ID(@tableName);   With indexCTE(partition_scheme_name , partition_function_name , data_space_id) As ( Select sps.name , spf.name , sps.data_space_id From sys.partition_schemes As sps Join sys.partition_functions As spf On sps.function_id = spf.function_id )   … Continue reading

Index Defrag Script

UPDATE: This script has been significantly updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/. Previously I posted that it’s important to keep your indexes in shape with weekly or (preferably) nightly defrags. Below is a script I use to execute nightly defrags in SQL 2005 Enterprise. I can’t claim complete credit… I believe this is a script I originally found on Microsoft and heavily modified to meet my needs. You may want to modify the script if you’re doing much with XML or LOB’s. Also keep in mind that this is hitting the sys.dm_db_index_physical_stats table to view fragmentation information, which can be resource intensive. If you’re not familiar with index defragmentation, check out “Alter Index” on Books Online. If Not Exists(Select object_id From sys.tables Where [name] = N’dba_indexDefragLog’) Begin   Create Table dbo.dba_indexDefragLog ( indexDefrag_id int identity(1,1) not null , objectID int not null , objectName nvarchar(130) not null , indexID int not null , indexName nvarchar(130) not null , partitionNumber smallint not null , fragmentation float not null , dateTimeStart datetime not null , durationSeconds int not null Constraint PK_indexDefragLog Primary Key Clustered (indexDefrag_id) );   Print ‘dba_indexDefragLog Table Created’; End   If ObjectProperty(Object_ID(’dbo.dba_indexDefrag_sp’), N’IsProcedure’) Is Null Begin Execute … Continue reading

Tips for Large Data Stores

I’m currently working on projects that involve the collection of massive amounts of data (i.e. terabyte-class, billion-row tables). One of the challenges of collecting so much data is the ability to report on the data quickly.  To this end, I plan to spend the next several blog posts discussing tips for designing and managing large relational data stores.  These are primarily design practices that have performed well for me within the various environments in which I’ve worked. Partitioning • Partitioning is the segregation of a single logical table into separate, identical physical structures. Basically, every table is stored in at least one partition; “partitioned tables” are merely comprised of many partitions. • Implemented correctly, partitioning can have dramatic improvement on read performance, index maintenance cost reduction, storage of large fact tables on separate disks, and data archiving. • If you’re not familiar with partitioning, check out Kimberly Tripp’s excellent white paper, Partitioned Tables and Indexes in SQL Server 2005. • I cannot rave enough about the impact partitioning has had within our environment. To take one of the most extreme examples of improvement, the execution time of one BI report decreased from 2.5+ hours to 20 minutes after we implemented … Continue reading

Scientific Notation with IsNumeric()

Be careful when using the built-in IsNumeric() function. When presented with a character string formatted as “number+D+number” or “number+E+number”, the function will return a value of 1 (true). This is because SQL Server evaluates [number][d,e][number] as scientific notation. For example, [cc lang="tsql"] Declare @dNotation char(10) , @eNotation char(10) , @standardText char(5); Select @dNotation = ’1d1′ , @eNotation = ’1e1′ , @standardText = ‘abcde’; Select IsNumeric(@dNotation) As ‘dNotation’ , IsNumeric(@eNotation) As ‘eNotation’ , IsNumeric(@standardText) As ‘standardText’; [/cc] … will return: dNotation   eNotation   standardText ————————————————- 1           1            0 I’ve seen a couple of different ways to approach this, and each have their merits. The most easy and elegant solution I’ve seen came from Dave C. — PatIndex(‘%[^0123456789]%’, columnName): [cc lang="tsql"] Select PatIndex(‘%[^0123456789]%’, @dNotation) , PatIndex(‘%[^0123456789]%’, @eNotation) , PatIndex(‘%[^0123456789]%’, @standardText); [/cc] I’ve also seen Replace() and user-defined functions used with great effect. Thanks to Dave C. for sharing his solution! HTH! Michelle

Permissions Script

Let’s get this blog thing started!  I sometimes find that appropriate permissions are not always assigned to stored procedures during creation.  This usually occurs when a proc is scripted and re-deployed in a hurry, or when a non-DBA decides to play in my sandbox.  I use this rather simple script to quickly find and grant all missing permissions. Declare @schema_owner varchar(20); Set @schema_owner = ‘dbo’;   Select [name] As ‘storedProcedure’, ‘Grant Execute On ‘ + @schema_owner + ‘.’ + [name] + ‘ To [insertDatabaseRoleHere];’ As ‘sqlCode’ From sys.objects With (NoLock) Where [name] Not In ( Select o.name From sys.database_permissions p With (NoLock) Inner Join sys.objects o With (NoLock) On p.major_id = o.object_id Inner Join sys.database_principals u With (NoLock) On u.principal_id = p.grantee_principal_id Where u.name = ‘[insertDatabaseRoleHere]‘ ) And [type] = ‘P’; This is especially useful for when you have a common database role that all stored procedures need to belong to; for example, infrastructures that share role membership to allow applications to execute procs.