:::: MENU ::::

Posts Categorized / DBA best practices

  • Apr 11 / 2012
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, Publications of Atif Shehzad on MSSQLTips.com, SQL Server Filegroups

Configuring the default filegroup for a database with multiple filegroups

By default a single file group exists for SQL Server databases.  It is primary and default file group for that database. However for better manageability and performance optimization we may be required to configure additional file groups for particular database. Data may be spread over multiple files in different file groups. SQL Server provides an option to change the default file group from primary file group to any other. Read here my article related to Using Multiple File groups for a Database and Changing the Default File group.

  • Mar 30 / 2012
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, SSMS tips and tricks, T-SQL Tips and Tricks

Move the columns to required position in SSMS result pane

It is always good to have a clear idea of filtered data and order of columns for any required result. However, consider the scenario that you have executed a resource intensive query and got the data in SSMS result pane. And after having a look at the result set you perceive that moving a column prior to a specific one may improve the report.
A option may be to change the order of columns in query and re-generating the result set to required order. But this would again consume resources for that resource intensive query. Best solution for changing column order in result set would be by dragging the column to required position.
Here is an example, i have retrieved result from AdventureWorks database and later moved the column [Phone] from last to second last position.

Swaping the columns in SSMS results pane

This simple tip may prevent the re-execution of query for column re-ordering purpose.

  • Mar 07 / 2012
  • 0
Data Modeling and Database Design, DBA best practices, dbDigger, Identity Columns, Performance Tunning and Optimization

Utilize the negative half of identity column data type

Identity columns are auto generated sequences used for ensuring row uniqueness at table level. Following data types of Numeric family may be used as data type of identity column.

  • int
  • bigint
  • smallint
  • tinyint
  • decimal/numeric

Above mentioned data types of numeric family may store negative values except the TINYINT. It is important to note that half of data types storage capacity of these data types is assigned to negative values. For example SMALLINT can have values from -32768 to 32767. It can hold -32768 to -1 negative values, a zero and 1 to 32767 positive values. Please also have a look at following table (Microsoft Technet) to get an idea about all numeric data types

Utilize the negative part of data type for identity columns

We may analyze that except the TINYINT data type, all other numeric data types have half capacity reserved for non negative values.

Point to Consider

Point to consider here is that identity column capacity should be fully utilized by including the negative part of data type also. Otherwise you are going to leave unused almost half of data type capacity. Very simple point is to give the lowest negative end of data type as SEED value of identity column. Consider the following demo

 -- Create table for demo  
 IF EXISTS   
 (SELECT * FROM sys.objects where name = 'IdentityDemo')  
 DROP TABLE IdentityDemo  
 GO  
 CREATE TABLE IdentityDemo (id SMALLINT identity(1 ,1), EmpName varchar(50))  
 GO  
 -- Insert values more than +ive part of smallint  
 insert into IdentityDemo values ('Atif')  
 GO 32769  

In above demo we used SMALLINT data type with seed and increment both 1, while creating the table. So inserting the values it failed for values more than 32767 and error was generated.

Error when identity column is saturated

Same may be verified by selecting the data from table

 -- Verify the inserted rows  
 SELECT * FROM IdentityDemo order by ID DESC  

The identity column is saturated and while negative position of data type is there unused. Now following demo would utilize the negative portion also and would have double capacity of storage

 -- Create table for demo with negative seed  
 IF EXISTS  
 (SELECT * FROM sys.objects where name = 'IdentityDemo')  
 DROP TABLE IdentityDemo  
 GO  
 CREATE TABLE IdentityDemo (id SMALLINT identity(-32768 ,1), EmpName varchar(50))  
 GO  
 -- Insert values more than +ive part of smallint  
 insert into IdentityDemo values ('Atif')  
 GO 34769  

This time insertion is successful with 34769 values. Can be verified by

 -- Verify the inserted rows  
 SELECT * FROM IdentityDemo order by ID DESC  

Same is the case for int, bigint and decimal/numeric also. So use the identity column as valuable resource and fully utilize it to avoid early saturation.

  • Nov 10 / 2010
  • 0
DBA best practices, dbDigger, Publications of Atif Shehzad on MSSQLTips.com, SQL Server Agent scheduled Jobs

Scripts for improving SQL Server Agent Jobs environment

In a recent article on MSSQLTips.com some very nice suggestions were mentioned by Jeremy Kadlec about improving the SQL Server Agent jobs environment. It tend me to apply these suggestions on my production server. I worked for this task on test server and shared the scripts created during whole exercise through this article. This article goes through process of efficient modification of SQL Server Agent jobs and job steps.

  • May 04 / 2010
  • 0
DBA best practices, dbDigger, SSMS tips and tricks

Customize SSMS startup environment for object explorer, query pane and activity monitor

After launching SQL Server Management Studio (SSMS) you always modify the environment by opening query pane or by opening/closing the object explorer. In SSMS i am also habitual to use activity monitor. Fortunately SSMS provides you option to modify the start up environment. At start up you can configure SSMS to open

  • empty environment
  • object explorer
  • query pane
  • both object explorer and query pane at same time
  • In SSMS 2008 open object explorer and activity monitor at same time

To configure your SSMS start up environment. Just go to Options in tools menu.

Open Options in SSMS

Now open ‘At start’ drop down list in produced options frame. It is under ‘General’ heading as marked in snap below.

Configure SSMS startup options

Select required environment and click Ok to save the settings. Next time when you would open SSMS you have your required environment there. Activity monitor of SSMS 2008 can be used with SQL Server 2005 instance.

Pages:1234567
Consult us to explore the Databases. Contact us