SQL Server Tips, tricks, blogs, articles, content

Who is online?  128 guests and 0 members
home  »  blogs  »  prince rastogi

SQLServerGeeks.com Blogs

Blogs RSS Feed

prince rastogi : Most Recent postings

prince rastogi

SQL Server: Filtered Statistics Update Issue

4/15/2014 6:06:40 AM by prince rastogi  -  Comments: 0  -  Views: [582]

Hi Friends, In my earlier blog, I just explain about the importance of filtered indexes. Link for that blog is mention below: http://sqlservergeeks.com/blogs/princerastogi/personal/599/filtered-indexes Some limitations are also there for filtered indexes. When we create indexes then by default it will also create statistics. Those statistics will be updated based on threshold values. Some of them are mention below which we will use in our blog today: 1. If number of rows in table <= 500 at th...

Read More

prince rastogi

SQL Server : Impact of checkpoint under full recovery mode

10/16/2013 5:49:27 AM by prince rastogi  -  Comments: 0  -  Views: [2227]

Hi Friends, We all knew that, under Full recovery model log truncation occurs due to transaction log backup while under Simple recovery model log truncation occurs due to checkpoints. But here I want to show you the impact of checkpoint on your database while database is in Full recovery model. Actually the concept is when you create any new database then by default the recovery model of that database will be full until you made any change on default setting. Here this database will be treated a...

Read More

prince rastogi

SQL Server : Space Required by Index Rebuild operation

7/26/2013 8:46:20 AM by prince rastogi  -  Comments: 0  -  Views: [5635]

Hi Friends, Today, I will show you that impact of index rebuild operation on data file. Actually when we perform clustered index rebuild operation than it will required almost same amount of space as of your clustered index. The reason behind this thing is very simple when we perform any index rebuild operation than SQL Server first create new index structure without deleting the old structure but after the creation of new index structure it will delete the old structure from data file. That mea...

Read More

prince rastogi

SQL Server: Server Level Audit using TSQL

7/22/2013 2:42:43 AM by prince rastogi  -  Comments: 0  -  Views: [2407]

Hi Friends, In my previous blog I describe about auditing and auditing setup using management studio. You can go to that blog by using below mention link: http://sqlservergeeks.com/blogs/princerastogi/personal/819/sql-server-server-level-audit-using-management-studio Today here we will setup the server level auditing using TSQL: Step 1: Create Server Audit Use master go CREATE SERVER AUDIT AUDITDEMO TO File (FilePath='E:\AuditLogs\' , MAXSIZE = 1024 MB , MAX_ROLLOVER_FILES = 5 , RESERVE_DISK_SPA...

Read More

prince rastogi

SQL Server: Server Level Audit using Management Studio

7/21/2013 1:22:31 AM by prince rastogi  -  Comments: 0  -  Views: [4452]

Hi Friends, When we are using SQL Server in our organization then most of the time we required Auditing. Microsoft provide Auditing feature from SQL Server 2008 onwards but this is an Enterprise feature. There are two types of Auditing which you can implement, first is Server level auditing second is database level auditing. Before SQL Server 2008, if you want to use Auditing then we implemented this by using various things like SQL Trace, DDL Triggers, login auditing. There are so many drawback...

Read More

prince rastogi

Database Restore with Snapshot - Backup Chain Breaker

6/11/2013 2:49:29 PM by prince rastogi  -  Comments: 0  -  Views: [2356]

Hi Friends, Today I will show you practical for one of the reason of Transaction Log backup chain breaker i.e. restoring database with database snapshot. Suppose I am having one database named as SQLTEST having one table named as xtSQLTest with some amount of data. --create database create database SQLTEST go USE SQLTEST go -- create a table create table xtTest ( id int identity(1,1) ) go --insert some data in the table insert into xtTest default values; go 100 Now take full backup and insert so...

Read More

prince rastogi

SQL Server: Handling Hierarchical data inside the database Part3: Index on HierarchyID

4/21/2013 10:32:06 AM by prince rastogi  -  Comments: 4  -  Views: [3784]

Hi Friends, Today we will see how we can implement Index on HierarchyID Column, and what benefit we can achieve by using these indexes. In My previous blogs on HierarchyID we saw how can we use HierarchyID Column as well as how can we search ancestors and descendant values for HierarchyID column. You can go through on previous blogs by using below links: http://sqlservergeeks.com/blogs/princerastogi/personal/782/sql-server-handling-hierarchical-data-inside-the-database-part1 http://sqlservergeek...

Read More

prince rastogi

SQL Server: Handling Hierarchical data inside the database Part2

4/11/2013 6:27:31 PM by prince rastogi  -  Comments: 0  -  Views: [3440]

Hi Friends, Today we will see how we can search ancestors and descendants in a HierarchyID column. In the previous blog you saw that how can we handle hierarchical data with HierarchyID data type. There we saw creation of table using HierarchyID data type as well as inserting the hierarchical data inside that column. You can go for previous blog with below link: http://sqlservergeeks.com/blogs/princerastogi/personal/782/sql-server-handling-hierarchical-data-inside-the-database-part1 Now run that...

Read More

prince rastogi

SQL Server: Handling Hierarchical data inside the database Part1

4/7/2013 7:30:03 AM by prince rastogi  -  Comments: 17  -  Views: [8424]

Hi Friends, Sometimes we need to handle hierarchical data in databases like trees where data elements are having relationship like parent and child. Before SQL Server 2008 it was complicated to deal with such kind of data. Inside SQL Server 2008 Microsoft introduces a new data type HierarchyID to handle such type of data and reduce the complexity. By using this data type you can also use various methods provided by SQL Server to deal with such data. Here we will see how can, we use it: USE Maste...

Read More

prince rastogi

SQL Server Buffer Pool Part 3 - Key Lookup versus Index Seek

3/21/2013 4:51:33 AM by prince rastogi  -  Comments: 0  -  Views: [6363]

Hi Friends, Today I am going to explain about the impact of non clustered indexes on buffer pool or you can also say it as key lookup versus index seek operation. Actually this is the continuation of SQL Server Buffer Pool Series. Link is mention below for previous blogs: http://sqlservergeeks.com/blogs/princerastogi/personal/737/sql-server-buffer-pool-part-1-deep-look-inside-buffer-pool http://sqlservergeeks.com/blogs/princerastogi/personal/757/sql-server-buffer-pool-part-2-impact-of-clustered-...

Read More

prince rastogi

SQL Server: Buffer Pool Part 2: Impact of Clustered Index on Buffer Pool

3/4/2013 12:28:57 PM by prince rastogi  -  Comments: 2  -  Views: [3551]

Hi Friends, Here, I am going to introduce you with a very interesting thing about clustered indexes. We all knew that when we execute a particular select query then the required data pages first comes in to the buffer pool. Actually SQL Server reads complete extent rather than individual data pages. But the interesting thing is SQL Server also reads some other adjacent extents which are not required to that particular select query in case of clustered index. This Blog is the continuation of my f...

Read More

prince rastogi

SQL Server: Fragmentation for the table having less than 24 data pages

2/17/2013 4:50:00 PM by prince rastogi  -  Comments: 2  -  Views: [4144]

Hi Friends, Here I am going to explain a problem which is asked by one of my friend. Problem: Let’s run the below query: USE master go create database PRINCE go USE PRINCE go create table xtprince ( id int identity(1,1), name char(8000) ) go insert into xtprince values('SQLGEEKS') go 8 create clustered index IX_CLUS_xtprince on xtprince(id) Here I have created a table xtprince, where one data page will contain one record only. Here this table contain 8 records i.e. 8 data pages. After inse...

Read More

prince rastogi

SQL Server: Buffer Pool Part 1: Deep Look inside buffer pool

2/6/2013 4:49:45 AM by prince rastogi  -  Comments: 0  -  Views: [8579]

Hi Friends, Today, i am going to explain various things about the sql server buffer pool which is also known as buffer cache. Buffer Pool consist of various type of cache like data cache, plan cache, log cache etc. Here data cache is the very important part of buffer pool which is used to store the various types of pages to serve particular query.Suppose if we run a particular select query on a table to show all data rows of that table. Then all the data pages of that table will be required to f...

Read More

prince rastogi

SQL Server : Plan Cache Optimization Using Optimization For Ad hoc Workloads

1/15/2013 7:00:11 PM by prince rastogi  -  Comments: 0  -  Views: [10477]

Hi Friends, There is a very good option in SQL Server 2008 for Performance Optimization, i.e. "Optimize for Adhoc Workloads". By using this option you can maximize the use of Plan cache. When your workload contains single use adhoc queries or batches then this option will provide you good performance improvement in terms of plan cache. Compiled plan storage depends on Parameterization option set for database which are 1- Parameterization Set Simple: This is the default option for SQL Server. 2- ...

Read More

prince rastogi

Part 3: Database Transaction Log - Virtual Log Files: Space Required to Rollback

12/28/2012 8:43:49 PM by prince rastogi  -  Comments: 0  -  Views: [2306]

Hi Friends, Today I am going to explain a very interesting thing about SQL server database log files. Which is every database transaction log file maintain some space to rollback the transaction as per the requirement of the transaction. We can understand this thing very clearly by using an example. So let’s start with the below example: step1: Create Databases USE [master] go create database TEST1 go create database TEST2 go step2: Now Run the below mention query: Use [test1] go create ta...

Read More

prince rastogi

SQL Server 2012 : Dynamic Management Views

12/22/2012 10:28:05 AM by prince rastogi  -  Comments: 1  -  Views: [2934]

Hi Friends, There are some new DMVs added in SQL Server 2012,Amongs of them two are sys.dm_server_services and sys.dm_os_windows_info.You can use these DMVs as per your requirements, where sys.dm_server_services provides information about services like SQL Server, SQL FullText Filter and SQL Agent.The output of sys.dm_server_services contain information about service is currently running or stop, startup type for that service like automatic,manual etc. We can also get the most important informat...

Read More

prince rastogi

SQL Server – Database Information using DBCC DBINFO & DBCC PAGE

12/11/2012 5:53:53 PM by prince rastogi  -  Comments: 0  -  Views: [6577]

Hi Friends, Today, I am focusing on the use of an undocumented command DBCC DBINFO. By using this command you can get some more interesting information about your database. Actually, DBCC DBINFO gives you the information about metadata of the database which you provide as parameter. To see the output you have to turn on the trace flag 3604. DBCC TRACEON (3604) GO DBCC DBINFO (SQLGEEKS) From the above output you can get the information about database id, create date, compatibility level, Last log...

Read More

prince rastogi

SQL Server - Part 2: Database Transaction Log - Virtual Log Files: Circular Behavior

12/2/2012 2:39:07 PM by prince rastogi  -  Comments: 0  -  Views: [3735]

Hi Friends, In my previous blog I just mention about what will be the number of Virtual Log Files at the time of creation of new database, where number of VLF files depends on the initial size of transaction log. As the size of transaction log increases then number of VLF Files will also increase, where number of VLF Files will depends on the Transaction Log file growth configuration. You can Read the previous blog from here: http://www.sqlservergeeks.com/blogs/princerastogi/personal/701/sql-ser...

Read More

prince rastogi

SQL Server - Part 1: Database Transaction Log - Virtual Log Files: DBCC LOGINFO

11/29/2012 4:51:54 AM by prince rastogi  -  Comments: 1  -  Views: [11675]

Hi Friends, Today, I am going to explain the internal of SQL Server database transaction log file architecture by using DBCC LOGINFO. Our Transaction log file is just divided into multiple Virtual Log Files known as VLFs. We cannot configure the number of VLF files and the size of VLF Files. When we create a new database then number of virtual log file depends on the initial size Transaction Log file i.e Case 1 : Number of VLF files are 4 when Transaction Log Size is less than or equals to 64 MB...

Read More

prince rastogi

SQL Server Database Backup Compression - Faster Disaster Recovery

10/12/2012 8:40:46 PM by prince rastogi  -  Comments: 0  -  Views: [36878]

Hi Friends, Today, I am focusing on very interested feature provided by Microsoft SQL Server for DBA people. This feature is “Database Backup Compression”. This feature first introduced in SQL Server 2008, only for Enterprise editions. This feature is also available in later versions. If we want to use this feature then we need to turn on this feature. There are some major benefits of using database backup compression: 1- Less backup time: after compressing the data SQL Server takes ...

Read More

prince rastogi

SQL Server 2012 Management Studio “Import and Export Settings"

10/5/2012 7:06:54 PM by prince rastogi  -  Comments: 0  -  Views: [7704]

Hi Friends, There is a very interesting option in SQL Server 2012 Management Studio “Import and Export Settings….”. This feature will allow you to Import and Export some SSMS settings as well as you can reset your SSMS settings to default also. There are various types of settings like Fonts and Colors, Find and Replace, batch separator etc. To use this feature just open the management studio 2012, go to options.. tab under Tools menu as shown below: This will open Import and E...

Read More

prince rastogi

SQL Server 2012 User Defined Server Role

9/22/2012 4:58:44 PM by prince rastogi  -  Comments: 0  -  Views: [4341]

Hi Friends, Today, I want to focus on a very helpful feature User Defined Server Roles in SQL Server 2012 (Denali). In SQL Server 2005 and SQL Server 2008, there are only nine fixed server roles to grant the permissions. These nine fixed server roles are shown below in figure: Now with SQL Server 2012 we can create user defined Server Roles using TSQL as well as SSMS. Creating Server role using TSQL: Here for testing purpose, I am creating a new role TestRole and granting Trace Event Notificatio...

Read More

prince rastogi

SQL Server - Partially Contained Databases in SQL Server 2012 (Denali)

11/15/2012 9:37:50 AM by prince rastogi  -  Comments: 7  -  Views: [3638]

Hi Friends, Today, I want to focus on a very good feature Partially Contained Databases of SQL Server 2012 (Denali). Problem: When we install an instance of SQL Server with a particular collation say X, but after the installation we create a database with a different collation say Y . Now, if we want to compare the data of a particular column in database table with the data of a particular table created in tempdb then it gives the error as shown below: Cannot resolve the collation conflict betwe...

Read More

prince rastogi

sql_server_covering_index_2: Adding Non Key Columns

8/28/2012 6:01:33 PM by prince rastogi  -  Comments: 2  -  Views: [5542]

Hi Friends, This blog is the continuation of my previous blog on Use of Covering Indexes... http://sqlservergeeks.com/blogs/princerastogi/personal/623/sql_server_covering_index_1 Today here I will explain Adding non key columns in the nonclustered indexes as well as what are the benefits of adding non key columns in comparison to adding key columns in nonclustered indexes. Let me explain this with the practical example. First create a table with the script as shown below: IF EXISTS (SELECT * FRO...

Read More

prince rastogi

SQL_Server_Covering_Index_1

8/14/2012 2:37:29 PM by prince rastogi  -  Comments: 2  -  Views: [3219]

Hi Friends, Today, here I just want to explain ‘what is Covering Index?’ as well as ‘why we use Covering Indexes?’ What is Covering Index? “Covering Index is a different case of index, where index contain all the data columns required to serve a particular query.” Why we use covering indexes? There are two ways of adding columns in nonclustered indexes to make covering indexes. 1- Adding only key columns in the indexes. 2- Adding non key columns in the indexes...

Read More

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Email Subscriptions

   Get the Most Recent Blogs in your inbox