Finding Missing Indexes and Unused Indexes using DMVs

Finding Missing Indexes: Dynamic management object Information returned sys.dm_db_missing_index_group_stats Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes. sys.dm_db_missing_index_groups Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes…

2

DDL Triggers and LOGON Triggers

DDL Triggers:  DDL triggers can be used for administrative tasks such as auditing and regulating database operations. Use DDL triggers when you want to do the following: ¾  You want to prevent certain changes to your database schema. ¾  You want something to occur in the database in response to a change in your database…

2

Plan Cache Analysis in SQL Server 2005

Plan Cache: The portion of the memory pool allocated to store Query execution Plan For how much memory is allocated for Plan Cache in different version of SQL server, please refer to my blog: – Understanding Query Plan Cache in SQL 2005 SP2 – why it’s changed from SQL 2005 RTM/SP1? A stored execution plan…

1

Understanding Query Plan Cache in SQL 2005 SP2 – why it’s changed from SQL 2005 RTM/SP1?

Query execution plan gets stored in Plan Cache – the portion of the memory pool allocated for storing execution plans.   There is a very good blog written by SQL Programmability team on why Query Plan Cache behavior changed in SQL 2005 SP2 vs. SQL 2000 and SQL 2005 RTM/SP1  – http://blogs.msdn.com/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx Based on this the…

1

Maximum Capacity Specification for SQL Server 2005 and 2008

I came across this link – http://technet.microsoft.com/en-us/library/ms143432(SQL.100).aspx.  Its amazing to see the max capacity specification for SQL Server.    These are for SQL Server 2008, but they haven’t changed from SQL Server 2005’s limit.      I would love to see systems with near capacity in below specifications: SQL Server Database Engine object Maximum sizes/numbers SQL Server (32-bit)…

0

Useful information about Guest Account in SQL Server

guest user When a database is created, the database includes a guest user by default. Permissions granted to the guest user are inherited by users that do not have a user account in the database. The guest user cannot be dropped, but it can be disabled by revoking its CONNECT permission. The CONNECT permission can…

0

Connecting to Remote Integration Services Server – info with missing things on msdn article

I found it hard way how to connect to Integration Services server remotely.     If you are part of sysadmin role on a server or if you are part of local administrators group on the server, there is no problem connecting to Integration Services server from your computer remotely.  But for others they get “Access is…

0

Connecting to multiple Data Sources using single Report

I have been looking for something like this for ages. This could be very useful to run a common report on a multiple servers.  http://technet.microsoft.com/en-us/library/ms156450.aspx Data Source Expressions:You can put an expression into a connection string to allow users to select the data source at run time. For example, suppose a multinational firm has data…

0

/3GB /PAE and AWE on 32-bit Systems

I was reading a blog post from Chad Boyd – http://blogs.msdn.com/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx.   He has very detailed and excellent information about /3GB PAE and AWE.    It made my understanding more clear.    Here is the summarized version: For 32-bit OS Memory Addressability Limitation: By default, the 32bit OS can only ‘see’ and use up to 4gb of memory,…

1

Data Scripter Utility for SSMS

You can generate a script for a data within a table – whole bunch of insert statement.   There is an external plug-in available at http://www.codeproject.com/useritems/enisey.asp.   I downloaded it and installed it on my machine, it works great.   After installing it you will see an option in your SSMS within Right-click menu off of a selected table…

2