MAXDOP – Maximum Degree of Parallelism

No Responses »
Jan 082010

Parallel INDEX Operation are available only in SQL Server Enterprise Edition.

MAXDOP can also be used a QUERY HINT Option

In a multiprocessor SQL Server System, this setting controls the number of processors you want to use in query execution or use in parallel Query execution.

When we execute a SQL Query, the Query processor in the SQL Server Engine, goes to work and determines among other factors, the processors to use for the Query. If the setting is at default, SQL Server will determine the number of Processors to use for a particular Query, depending upon the complexity of the query.

Default setting is = 0  (use at will or all) – which tells SQL Server to use all the available processors as desired for the particular query.

This setting can be changed in many ways and some are:

using SSMS: Right click on Server –> Properties, then click on Advanced.

image

or using sp_configure in Query Analyzer.

sp_configure ‘show advanced options’,’1′
reconfigure with override

again specify sp_configure and t will produce these results. you can change the value if you are an expert, otherwise leave it at default as it best perform when at default.

image

Now using the MAXDOP in the SELECT Query as Query Hint is also possible as with this example from BOL:

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2); – tells the query to use 2 processors.

SQL Server will self detect the best degree of parallelism based on :

  • If sufficient result is the be produced, i.e. sufficient number of rows are present as a result.
  • Type of Query or INDEX used. (Large Joins and Aggregates)
  • If sufficient Threads are available for the operation. (More threads are required to execute a Query in parallel than when it is executed serially, if the thread requirement is not met, by sql server it decreases the parallel execution or completely abandons the parallel execution).

A bit complex internally to SQL Server, so lets us concentrate on our Queries for now and if we want to control the num of processors used for a Query.

I would suggest to give this a try if you have more then 4 processors. If you have a total of 4 Processor(Physical+Hyper threaded), leave this to default.

But i had benefitted on one of my Servers (running solarwinds network performance monitoring services) Running Extensive transactions. I Turned off the Max Degree of Parallelism and the server was faster with no locking scenarios than before the setting was at default of 0.

sp_configure ‘Max degree of parallelism’,’1’ – use this to turn off the parallelism.

Pl read more before taking any action on your production servers. The information mentioned above benefitted me, and does not mean it will be same for you. So try the things on your TEST Servers before ever Thinking of the production boxes.

Posted by maninder at 11:54 am Tagged with: , ,

Long Distance (WAN) Queries are SLOW

2 Responses »
Nov 232009

Ok, i meant was if your servers are located at remote locations(Across WAN) or Over slow networks, like our are located at Colorado and we are at Pittsburgh, Definitely we were having the issue of SLOW QUERY Responses.

What do you look for in this kind of a incident, when everything is working good as per the SYSADMINS and NETWORK ADMINS, the DBA’s are left to wander in the dark, with out any proper responses.

I started playing with protocols Named Pipes and TCPIP but hey what i found was even if you select the TCPIP in the SSMS connection box, it resolved to Named Pipes during the connection.

I tried again and with the same results and same slowness of the Query.

Ok how slow were the results: Well PRE-MOVE to Colorado, we were at a baseline of 28000 rows in 10 sec with a select * from <table name>

After the move we were only able to retrieve 400 rows in 10secs.. Well isn’t that a huge difference, and as a DBA, my Judgment was at risk and so was my performance on the EDGE.

Now I checked the CLICONFG at START-> RUN. Changed the connection to TCPIP and LO! Gods Spell worked and it worked like a charm. Well these are some hard earned but interesting and helpful lessons learned on the JOB.

http://msdn.microsoft.com/en-us/library/aa178138(SQL.80).aspx

image

image

image

Posted by maninder at 8:55 pm Tagged with: , ,

How long has SQL Server been Running? (instance)

No Responses »
Oct 222009

Several ways to check how long SQL Server has been running or to REPHRASE this Question: When was SQL Server last restarted.
In Windows–> Administrative Tools–>Event Viewer look for SQL Server Restart info.
Run a query in analyzer (Select * from master.dbo.sysprocesses where spid=1)

This is the first system ID which connects into the SQL Server, after the SQL has been restarted.
Mind it; the login_time will be off by a Few minutes (sometimes) vs. the Windows Event Viewer, which will anyway show you the exact time the SQL server Started.

OR
Check the SQL Server Error Log and see the DATE for the first logged Event in the Error Log; this should match with the Event Viewer and should be the time When SQL Server was started.
But again: SQL Server Error Log can be Recycled without Restarting SQL Server,

Posted by maninder at 11:01 pm Tagged with: , , , ,

Orphaned Database users (issues and solutions)

3 Responses »
Jun 122009

There are several situation where the database user mapping will be lost to sql logins.

  • sometimes, after a Database is restored from backup or across sql servers.
  • After the Logins are moved across SQL Servers.
  • After Dropping and re-creating a SQL Server Login.
  • After Re-naming and SQL Server Database User / Server Login.

Out of this the most common scenario is the Database Restore and Move Logins.

Now the IMP part is, How to re-do the mapping between the Database Users and Logins.
Praise BOL: Microsoft has included a stored procedure for Just that.

sp_change_users_login (members of db_owner fixed database owner can execute this sp)

Lets look at the magic and logic of using this SP. Here are some simple steps to perform, before going further.

use <Database>;
sp_change_users_login ‘report’ : This will report and orphaned users in the database.

sp_change_users_login ‘update_one’,’<user>’,’<user>’  : This will re-map/correct the mapping of the user vs Login. you can map the existing database user to a new SQL Server Login (sp_change_users_login ‘update_one’,’user’,’newloginuser’) or Same Login as above.

The UPDATE_ONE : Links the Specified (one user at a time) user to a existing login or new login.

Instead of UPDATE_ONE, you can use ‘AUTO_FIX’ :  Which will help map the Database user to same login or new logins with password, if the login does not exist.

sp_change_users_login ‘Auto_Fix’,’dbanation’,NULL,’passw0rd’ : This will create a Login based on the database user with the password.

What do you do if there are hundreds of user in the database.? Well something like this, while using the RESULTS to TEXT option in SSMS.

select ‘EXEC sp_changeuserslogin ‘++””+’update_one’+””+’,'+””+u.name +””+’,'+””+u.name+””+CHAR(10)+’ GO’ from master..syslogins l
right join sysusers u
on l.sid = u.sid
where l.sid is null
and issqlrole <> 1
and isapprole <> 1  
and ( u.name <> ‘INFORMATION_SCHEMA’ and u.name <> ‘guest’  and u.name <> ‘system_function_schema’ )

The Results to TEXT option will output a proper statement with the GO Clause in a new line. (This will have no affect while in Grid View)

This will generate the EXEC Statements for all the users with update_one clause. Copy and paste this in another Query window and Execute and Enjoy the Time Saved:

Posted by maninder at 11:05 pm Tagged with: , ,
© 2010 D B A N A T I O N Suffusion WordPress theme by Sayontan Sinha
Better Tag Cloud