4 Nov

MVP Community Day \ PowerShell Day 9th Nov’2013 | Gurgaon

Dear Friends,Community_Day_9_nov_2013_Sarab

This Saturday (9th Nov’2013) South East Asia MVP Community is celebrating MVP Community Day, a day dedicated to Knowledge sharing & Learning.

 

On this very day New Delhi PowerShell User Group is organizing an event in Gurgaon, and luckily my session “Managing SQL Server using PowerShell” has been selected. Aman Dhally (PowerShell MVP) is also taking a session on Managing Event Logs using PowerShell.

This session is for all those who want to learn PowerShell and want to know more about its integration with Event Logs etc. Please use the below link to register for the event.

Register here : https://www.eventbrite.ie/event/9156263631

Session Details
Time Session Topic Presenter
9:30 – 10 AM Start of Registrations  
10  – 11 AM Managing SQL Server using PowerShell Sarabpreet Singh Anand
(
MVP SQL)
11 -11:15 AM Tea Break  
11:15 – 12:15 PM Managing Windows Event Logs using PowerShell Aman Dhally
(
MVP PowerShell)

 

See you at the event.

Regards
Sarabpreet Singh Anand & Aman Dhally
http://newdelhipowershellusergroup.blogspot.in/


17 Oct

Find Users with DBOwner Database role

Scenario: Auditors need a list of Users who have DBOwner Database role on any of the database which are hosted on Tier 1 Servers. Giving this information was Very easy for few servers where the DBCount was around 10 to 20, but we also have 8 SharePoint servers each hosting 200-350 DBs. Extracting this information on all these Servers was a pain, to ease things I created a script which can get all this information on a single execution.

This script can help you to extract the list of users from all databases who have DBOwner role. Once you execute the query you’ll get three column output for the query, three columns are: Database_Name, Role_Name, DBUser_Name. All the column names are self-explanatory.

You can download the script from this link: Find_DBOwners_from_All_Databases.sql

This is how the output will look like:

Find_DBOwner_all_Dbs

 

Note: Tested successfully on SQL Server 2012.

Enjoy!
Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox


2 Sep

SQLServer Day 14th September 2013 | Gurgaon

Hi Friends,

SQLServerGeeks.com cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And this time we are coming to two cities in India; Gurgaon & Hyderabad. This is yet another opportunity to learn.

The Sessions are Troubleshooting Deadlocks, How to use SQLDiag and SQL Nexus, Hekaton (In-Memory) Database SQL Server 2014 & lesser known Enhancements of SQL Server 2012. Events are hosted at Microsoft premises in respective cities so that your experience is fabulous.

Register Fast and block your seat. It is absolutely free. Also forward this email to your friends and colleagues to spread awareness.

 

To Register visit: http://sqlserverdaygurgaon14sep2013.eventbrite.com/

14-september-multicity-event

See you at the event!


4 Aug

Using Covering Index

A Non Clustered Index which contains all columns which are referred by a Query (in Select, Where or Join) is called Covering Index.

In other words

When all columns which are required to satisfy the query are included in a non-clustered Index to avoid extra key lookup that index is known as Covering index.

This type of index can effectively improve the query performance. To understand the reason why Covering index helps in gaining extra performance let’s do some testing.

In this example, I’ll be creating two identical tables Stock & Stock1 with exactly same data and number of rows. To test the performance benefits, we’ve to create different type of indexes on both tables. On first table we’ll create an Index with all key columns and on Second table we’ll create a Covering index which will also include the non-key column. Let’s see whether it impacted the performance or not.

This is the query which we need to test: (Queries are given below)

select PID, Qty, calories, PName from Stock where Qty>=11 and calories< 200

On Table Stock we created a Clustered Index on PID and a non clustered index on Qty and Calories whereas on Table Stock1 we created a clustered Index on PID and a NON Clustered Index on Qty and Calories along with these two columns we also included a non-key column PName to convert this index into a covering index.

In First table, the Query uses the non-clustered index to get PID,Qty and calories and once all these values are available SQL Engine will search the PName column at the leaf node of the clustered Index (Basically it will be a lookup against the Clustering key which is PID) . Now if we could avoid traversing Clustered index to get relevant PName values we can easily improve the query performance; to do so We created another table Stock1 where the non-clustered index was created on Qty and calories but PName (a non-key column) was included in the index; Since the non clustered index already has the PName values now there is no need to traverse the Clustered index and we can save those resources.

When we executed both these queries after enabling Statistics IO ON; we saw a decrease in Logical Reads from 43 to 39. I know the numbers are not too much and theres not a major difference but there is definitely a improvement which depends heavily on Number of records & selection criterion.

image

 

Then we compared the Execution plans and saw that the first query on Stock table where we don’t have non-key column in the index that is taking 93% resources compared to second table query which is just 7%.

image

 

Moreover, if you compare the IO and CPU Cost there is a drastic improvement. Hence we can conclude by saying that performance wise Covering indexes are much better then any other normal Non Clustered Index.

image    image

 

In-case, you want to test this you can use the below mentioned query. (Don’t execute queries on your production box, these queries are provided as is without any warranty)

create database Test_Sarab
go
use test_sarab
go

create table Stock
(
PID int Identity primary key,
Qty int,
calories int,
PName varchar(30)
)

create table Stock1
(
PID int Identity primary key,
Qty int,
calories int,
PName varchar(30)
)

insert into Stock values
(10, 267, ‘Doughnut Custard’),
(11, 410, ‘Doughnut Iced Glazed’),
(11, 256, ‘Doughnut Jam’),
(11, 266, ‘Muffin Blueberry’),
(11, 149, ‘Breakfast Muffin’),
(13, 401, ‘Muffin Chocolate Chip’),
(9, 141, ‘Wholemeal Muffin’),
(13, 78, ‘Brown Bread’)
go 1001

insert into Stock1 values
(10, 267, ‘Doughnut Custard’),
(11, 410, ‘Doughnut Iced Glazed’),
(11, 256, ‘Doughnut Jam’),
(11, 266, ‘Muffin Blueberry’),
(11, 149, ‘Breakfast Muffin’),
(13, 401, ‘Muffin Chocolate Chip’),
(9, 141, ‘Wholemeal Muffin’),
(13, 78, ‘Brown Bread’)
go 1001

create index Id2 on Stock (Qty, calories)

create index FinalIDx on Stock1 (Qty, calories) include (PName)

–dbcc dropcleanbuffers

SET STATISTICS IO ON;
select PID, Qty, calories, PName from Stock where  Qty>=11 and calories< 200

                       select PID, Qty, calories, PName from Stock1 where  Qty>=11 and calories< 200

 

Thanks,

Sarabpreet Singh Anand


25 Jul

SQL Tip-4 | Do you know you can increase number of archived Errorlogs

By default, SQL Server keeps only seven error logs; Errorlog and Errorlog.1 to Errorlog.6. The name of the current, most recent log is Errorlog with no extension. In my SQL Tip-1 I advised why you should recycle your errorlog on frequent basis and how. But as we know when you recycle errorlogs SQL Server makes an archive of current errorlog and push it down the stack one after another and keep only 7 total error logs (1 current and 6 archived) – in this process we’ll very soon reach a limit of 6 Archive errorlogs retention and will start loosing error logs.

In some cases (for instance Tier1 DB Servers), you may want to keep more than six archived error logs so that you can read the past entries. For all those servers you can very well increase this retention policy. You can configure the retention limit between 6 to 99. Here is a step by step How to blog for the same.

Since the retention limit is being maintained in a registry key, In Order to change the Archive error log Retention limit on your SQL Server you have two options:

  1. Using command
  2. Using SSMS GUI Window

Changing Retention limit in Registry using command: There are n number of ways to change a key value in Registry but since we are SQL Server Admins\Dev’s let’s utilize the inbuilt stored procedure to change registry entries from SQL itself:

USE [master]
GO
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 12
GO

In the above code, we are changing the retention value to 12. That means from now onwards SQL Server will maintain 13 total errorlogs (1 current and 12 archived)

Changing Retention limit in Registry using SSMS GUI Window: You can also change this retention value using GUI Window very easily.  Let’s take a look at the below screenshot of one of the servers where the Retention was set to 6 (By Default Value)

1

 

Now Right click on SQL Server Logs & choose configure

2

 

This is the default screen you’ll get if you’ve never changed this value.

3-default

 

Before you can change the limit you need to select the checkbox & once it is selected you can now provide any number between the range of 6 to 99.

4

 

Screenshot of one of the servers where I changed the limit and cycled error logs to see whether it is capable of holding higher number of archived logs or not.

5

I hope this helped you, and now you can easily change the retention if required.
Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox


18 Jul

SQL Tip-2 | Make Sure the startup type of your SQL Service is Automatic*

I know this one is very basic but in the recent past I noticed a couple of environments where the startup type of SQL Server Agent was set to manual which resulted in lot of issues.

Long story cut short – if your SQL Server is not clustered make sure the startup type is set to Automatic so that services can come online after a system reboot. Always use SQL Server Configuration Manager to change any property (including startup type) for any SQL related service.

Incase your SQL Server is clustered its better to keep the startup mode to Manual so that cluster service can take a decision & start the relevant node’s service based on Quorum.

 

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox


9 Jul

SQLServer Day 20th July 2013 | Gurgaon & 3rd Aug 2013 | Bangalore

Hi Friends,

SQLServerGeeks.com cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And this time we are coming to two cities in India; Gurgaon & Bangalore. We bring you 5 rock star speakers and 5 intriguing sessions. This is yet another opportunity to learn maximum from SQLServerGeeks.com across India. Sessions are spread across Business Intelligence, Developer & DBA tracks, so there is something for everyone to come and learn. Events are hosted at Microsoft premises in respective cities so that your experience is fabulous. Register Fast and block your seat. It is absolutely free. Also forward this email to your friends and colleagues to spread awareness. (For clarifications, please write to mamita<at>peoplewareindia<dot>com) !

To Register visit: http://sqlserverdaygurgaon20july2013.eventbrite.com/

Hurry Up –Register Now – you definitely don’t want to miss this event.

SQLServerGeeks-20-july-2013

See you at the event.