Saturday, May 26, 2012

Recompile ALL stored procedures in a database

Sometime after database maintenance such as update statistics or re-index need arises to recompile all stored procedures in that database. Following script can be used to recompile ALL stored procedures in a database




SET NOCOUNT ON

DECLARE @SPName varchar(128)
DECLARE @Owner varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @TableListLoop int
DECLARE @List table
(RecID int IDENTITY (1,1),
OwnerName varchar(128),
TableName varchar(128))

INSERT INTO @List(OwnerName, TableName)
SELECT u.[Name], o.[Name]
FROM sys.objects o
INNER JOIN sys.schemas u
 ON o.schema_id  = u.schema_id
WHERE o.Type = 'P' --and o.name not like '%retire%'
ORDER BY o.[Name]


SELECT @TableListLoop = MAX(RecID) FROM @List


WHILE @TableListLoop > 0
 BEGIN

 SELECT @SPName = TableName,
 @Owner = OwnerName
 FROM @List
 WHERE RecID = @TableListLoop

 SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @Owner + '.' + @SPName + ']' + char(13)

 -- SELECT @CMD1
 EXEC (@CMD1)


 SELECT @TableListLoop = @TableListLoop - 1
END

SET NOCOUNT OFF
GO

Friday, May 25, 2012

How to find number of processor cores?


Processors come in different flavors suck as dual core quad core etc. It is difficult to figure out how many core CPUs are installed on the machine. So here are some of the ways to find number of CPU cores.

If you have SQL Server installed on the machine then there is a system procedure available to find out the processor cores.

exec master.dbo.xp_msver processorCount

For more details checkout this link


If SQL server is not available on the machine then you can check in registry under following entry

HKEY_LOCAL_MACHINE\HARDWARE\DESCRIPTION\system\CentralProcessor

Each core will have a separate entry under CentralProcessor so based on number of entries you can find number of cores.

You can also compare it with another registry entry under

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment\NUMBER_OF_PROCESSORS

Microsoft also provides a command line utility for getting server information. It is called CoreInfo and more details are available here


On command line you can use following and see the output in coreinfo.txt

coreinfo.exe > coreinfo.txt

How does SQL Server Licensing work and how to figure out what type of license you need?


As a DBA it is unlikely that you will be asked to work on licensing for SQL Server. But unfortunately (Or fortunately since I learnt new things) I was tasked to do the same.

First of all let’s see what the different types of licenses Microsoft offers are (We will consider SQL Server 2008 for this discussion)

SQL Server 2008 is available under three licensing models:

1.      Server plus device client access license (CAL)
2.      Server plus user client access license (CAL)
3.      Processor license

So how do you decide what type of license you need?

Server License: Every physical host which is running SQL Server needs a server license. SQL Server can be either database, Analysis services, Integration services or reporting services.

Device CAL: If your environment has fixed number of devices (PC, workstations, mobile devices etc.) which will connect to SQL Server then device CAL is most suitable for you. A service plus device CAL is most suitable for environments where multiple users use same workstations (e.g call centers)

User CAL: A user like employee, customer etc. requires access to SQL server then a user CAL is required for each user. Let’s say there are 10 employees and 50 customers then you will need 60 user CALs. Now please note that each of these users can connect to SQL server from ANY number devices. Also let’s say there are 3 SQL instances (1 database 1 SSRS and 1 SSAS) then each user will require a single user CAL to connect to all the 3 instances irrespective of host on which they are residing. All the 3 instances have to be of same SQL version.

Processor CAL: This is little different from device and user CAL. Let’s say you have a web application and number of users connecting is not fixed. In such situation you need to get processor CAL. 1 processor CAL is required for each processor installed on host running SQL Server (including any of its components like database, reporting services, analysis services or integration services) Number of users is unlimited and number of devices is also unlimited and connections can be from within the organization or outside (e.g via website).


If you already have a environment setup and you need to figure out the number of users connecting or number of devices connecting then use either of the following queries.

--FOLLOWING QUESY WILL GIVE ALL LOGINS WHICH HAVE ACCESS TO SQL SERVER
--FOR WINDOWS GROUPS YOU WILL HAVE TO MANUALLY FIND OUT NUMBER OF USERS IN THAT GROUP THROUGH ACTVE DIRECTORY SERVICE
SELECT *
FROM master.sys.server_principals
where type_desc in (
'WINDOWS_GROUP',
'SQL_LOGIN',
'WINDOWS_LOGIN'
)

--FOLLOWING QUERY WILL GIVE DISTINCT USERS CONNECTED TO A SQL SERVER INSTANCE AND NUMBER OF SESSIONS
SELECT  login_name ,
        COUNT(session_id) AS [session_count],getdate() logdate
FROM    master.sys.dm_exec_sessions
GROUP BY login_name

--FOLLOWING QUERY WILL GIVE NUMBER OF DEVICES CONNECTED TO A SQL INSTANCE ALONG WITH NUMBER OF CONNECTIONS
SELECT  ec.client_net_address ,
        es.[program_name] ,
        es.[host_name] ,
        es.login_name ,
        COUNT(ec.session_id) AS [connection count]
FROM    MASTER.sys.dm_exec_sessions AS es
        INNER JOIN MASTER.sys.dm_exec_connections AS ec
                                   ON es.session_id = ec.session_id
GROUP BY ec.client_net_address ,
        es.[program_name] ,
        es.[host_name] ,
        es.login_name
ORDER BY ec.client_net_address ,
        es.[program_name] ;

SQL Server license covers all components which come with it including SSRS, SSIS, SSAS.

Tuesday, May 22, 2012

Find auto-created SQL Server statistics


Based on workload SQL server will create statistics on various columns of tables. Use following script to find out auto-created statistics by SQL Server.

SELECT name, OBJECT_NAME(OBJECT_ID)
FROM sys.stats
WHERE auto_created = 1 and OBJECT_NAME(OBJECT_ID) like 'TableName';

Following command will remove statistics on a perticular table

DROP STATISTICS TableName.StatisticsName

Monday, May 14, 2012

MS SQL Server 2008 cluster Vs MySQL 5.0 Cluster

Here is a high level comparison between Clustered Microsoft SQL Server 2008 vs MySQL NDB Cluster


Feature
MS SQL Server 2008
MySQL Cluster 7.21
Technology used
Windows Clustering Technology
N(Network)DB(Database) Cluster technology
Resources
Shared within the cluster nodes
No shared resources
Operating System
Windows
Oracle Linux (4,5,6)
Solaris 10
Solaris 9
Red Hat Enterprise Linux (4,5,6)
SuSE Enterprise Linux (10,11)
Microsoft Windows 7
Microsoft Windows Vista
Microsoft Windows 2008 Server (incl R2)
Microsoft Windows 2003 Server
Microsoft Windows XP
Number of physical hosts
Minimum 2 physical hosts
Minimum 6 physical hosts
Load Balancing
Single active node without load balancing
Multiple MySQL active nodes and data nodes resulting in load balancing
Memory
Minimum 512 MB (2GB or greater recommended)
1 GB per node
Available Disk
Minimum 10 GB (40 GB or greater recommended)
3 GB per node
Processor
Minimum 1 GHz (x86), 1.4 GHz (x64)
(2 GHz or faster Recommended)
Intel/AMD x86, UltraSPARC
Network


Automatic Failover
Supported
Supported
Hardware footprint
Less because of shared resources
More because of share nothing cluster
Software requirements
Supports all software which x64 compitable
Supports all softwares with x32 and x64 compatibility
Geographically Dispersed cluster
Supported in Windows Server 2008 onwards
Supported in MySQL Cluster 7.2 onwards
Hosting on Virtual Machine
Supported
Supported
Cluster monitoring tools
Cluster Administrator comes as built in tool
Cluster Manager. Not open source and comes with CGE (Carrier Grade Edition)
Code Compilation
No required
Available as compiled or NDB code can be locally compiled
Embedded database capabilities
Available as compact and express editions
Supported using embedded libraries
Scalability
Scalability using built in tools such as resource governor, hot-add memory and CPU, hosting multiple instances
Scale up on single machine or across multiple servers by adding MySQL and/or data nodes

Friday, May 11, 2012

How to find current maximum identity seed and check remaining values based on data type


How to find current Identity value and check remaining values based on data type

Few days back I came across situation when one of the tablein production was INT with identity defined on it. However as we know the limitof INT is 2147483647 and when maximum value crossed 2147483647 then followingerror started showing up

Msg 8115,Level 16, State 1, Line 1
Arithmeticoverflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Now I went ahead and changed it to BIGINT (Limit 9223372036854775807)and everything was fine after that.

So I decided to create an alert which will tell me if any ofthe columns is reaching the limit of the assigned data type. So I wrotefollowing SP

This SP has input parameter of threshold at which we wantthe alert to trigger. It will also log the entries in a logging table. Thetable will log following details

DATABASE_NAME
TABLE_NAME
COLUMN_NAME
DATA_TYPE
MAX_VALUE
REMAINING_VALUES
PERCENTLEFT
STATUS
SERVER_NAME
LOGDATE

  1. SP will run on all the databases on a server
  2. It will first find all tables with IDENTITY column
  3. Find data type of the column
  4. Find current maximum value
  5. Compare the current maximum value with the limit of the data type of that column
  6. Based on the threshold find out which tables are coming close to the upper limit
  7. Load the data in a logging table
  8. Email details of tables which are above the threshold

Here is the code for the SP

USE MYadminDB
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ES_IDENTITY_VALUE_CHECK]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].ES_IDENTITY_VALUE_CHECK
GO
create PROCEDUREES_IDENTITY_VALUE_CHECK @threshold decimal(3,2) = NULL
AS
SET NOCOUNT ON
--EXEC MYadminDB.DBO.ES_IDENTITY_VALUE_CHECK 0.90
Create Table #identityStatus
(
DATABASE_NAME varchar(256)
,TABLE_NAME varchar(256)
,COLUMN_NAME varchar(256)
,DATA_TYPE varchar(256)
,LAST_VALUE bigint
,MAX_VALUE bigint
);

Execute sp_msforeachdb '
Use[?];
INSERTINTO #identityStatus
select''?'' AS [DATABASE_NAME], T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE,IDENT_CURRENT(T.TABLE_NAME) as LAST_VALUE
,Case
WhenC.DATA_TYPE = ''tinyint'' Then 255
WhenC.DATA_TYPE = ''smallint'' Then 32767
WhenC.DATA_TYPE = ''int'' Then 2147483647
WhenC.DATA_TYPE = ''bigint'' Then 9223372036854775807
EndAs [MAX_VALUE]
fromINFORMATION_SCHEMA.COLUMNS C
JOININFORMATION_SCHEMA.TABLES T
onT.TABLE_TYPE = ''BASE TABLE''
andT.TABLE_NAME = C.TABLE_NAME
ANDCOLUMNPROPERTY(object_id(T.TABLE_NAME), C.COLUMN_NAME, N''IsIdentity'') = 1
WHERET.TABLE_NAME NOT LIKE ''%_STG''
'

--DEFAULT THRESHOLD TO 85%
IF @thresholdIS NULL
SET@threshold = 0.90;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IDENTITY_VALUE_WARNINGS]') AND type in (N'U'))
CREATE TABLE [dbo].[IDENTITY_VALUE_WARNINGS](
[DATABASE_NAME] [varchar](256) NULL,
[TABLE_NAME] [varchar](256) NULL,
[COLUMN_NAME] [varchar](256) NULL,
[DATA_TYPE] [varchar](256) NULL,
[MAX_VALUE] [bigint] NULL,
[REMAINING_VALUES] [bigint] NULL,
[PERCENTLEFT] [real] NULL,
[STATUS] [varchar](30) NOT NULL,
[SERVER_NAME] [nvarchar](128) NULL,
[LOGDATE] [datetime] NOT NULL
) ON [PRIMARY]
ELSE
DELETE FROM IDENTITY_VALUE_WARNINGS
WHERE CONVERT(VARCHAR(12),LOGDATE,112) = CONVERT(VARCHAR(12),GETDATE(),112)

INSERT INTO IDENTITY_VALUE_WARNINGS
SELECTDATABASE_NAME,TABLE_NAME,COLUMN_NAME,DATA_TYPE,MAX_VALUE,(MAX_VALUE-LAST_VALUE) AS REMAINING_VALUES
,Case
WhenLAST_VALUE < 0 Then100
Else (1 - Cast(LAST_VALUE As float(4)) / MAX_VALUE) * 100
End As [PERCENTLEFT]
, Case
When Cast(LAST_VALUE As float(4)) / MAX_VALUE >=@threshold
Then'WARNING: APPROACHING MAX LIMIT'
Else 'VALUE IS OK'
End As [STATUS]
,@@SERVERNAME AS SERVER_NAME
,GETDATE() ASLOGDATE
FROM#identityStatus

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IDENTITY_VALUE_WARNINGS_RESULTS]') AND type in (N'U'))
DROP TABLE [dbo].IDENTITY_VALUE_WARNINGS_RESULTS


SELECT
CONVERT(VARCHAR(10),DATABASE_NAME) AS DATABASE_NAME,
CONVERT(VARCHAR(25),TABLE_NAME) AS TABLE_NAME,
CONVERT(VARCHAR(25),COLUMN_NAME) AS COLUMN_NAME,
CONVERT(VARCHAR(10),DATA_TYPE) AS DATA_TYPE,
MAX_VALUE,
REMAINING_VALUES,
PERCENTLEFT,
CONVERT(VARCHAR(30),[STATUS] ) AS STATUS
INTOIDENTITY_VALUE_WARNINGS_RESULTS
FROMIDENTITY_VALUE_WARNINGS
WHEREPERCENTLEFT < @threshold
ORDER BY DATABASE_NAME,TABLE_NAME



Declare @sub varchar(100)
Declare @qry varchar(1000)
Declare @msg varchar(250)
Select @sub = @@SERVERNAME + ' : DATA TYPE LIMIT CHECK ' +Convert(Varchar(10), GetDate(), 101)
Select @msg = 'RUN FOLLOWING QUERY TO GETRESULTS' + CHAR(10) + CHAR(10) +
'SELECT* FROM MYadminDB.DBO.IDENTITY_VALUE_WARNINGS_RESULTS ORDER BYDATABASE_NAME,TABLE_NAME' + CHAR(10) + CHAR(10)
Select @qry = 'SET NOCOUNT ON
SELECT* FROM MYadminDB.DBO.IDENTITY_VALUE_WARNINGS_RESULTS ORDER BYDATABASE_NAME,TABLE_NAME'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail',
@recipients ='admin@company.com',
@body =@msg,
@subject =@sub ,
@query =@qry ;


Wednesday, May 9, 2012

Using GUID as primary key and/or clustered index


We should avoid using column of type GUID (uniqueidentifier) for primary keys and for creating clustered index (In our case usually all Primary keys are also clustered)

The main reasons are

  1. GUID is string value so not optimal for performance
  2. Requires more storage (INT 4 bytes, BIGINT 8 bytes, GUID 16 bytes) for data and indexes. Since all non clustered indexes will be based of clustered index key if GUID is used as PK clustered then 16 bytes per key multiplied by all non clustered index entries
  3. Since GUID is server wide; it is highly likely that data change will cause higher index fragmentation than enum incremental values.
  4. Data comparison operations such as joins and where clause will be an overhead compared to enum comparison (INT/BIGINT)
  5. DB buffer cache will consume additional space while buffring the data sets which include GUID

However we will benefit from using GUID from a data replication perspective since merge replication is easily supported using GUID.