26 Nov


Greatest articles

aul White said:

Something that can work well in many environments, assuming a primarily OLTP-type workload:

1. Server-wide setting to MAXDOP = 1

2. Cost threshold set to zero

3. Use the MAXDOP = N query hint on code that benefits from parallelism (even if it’s just MAXDOP 2 to allow bitmap operators).

I also would dearly love to see an OPTION (USE_PARALLEL_PLAN) hint in SQL Server, but I can quite see why the SQL Server team would be reluctant to do this – sigh.
January 26, 2010 6:02 AM

Jonathan Kehayias said:


Your comment is exactly what Bob Ward said not to do in his Wait Stats Session at PASS. In addition ‘cost threshold for parallelism’ is ignored as is the MAXDOP hint when you set the server wide setting for ‘max degree of parallelism’ to 1, so your #2 and #3 are overridden all the time by #1.
January 26, 2010 2:33 PM

Paul White said:


That is absolutely *not* correct. A query hint MAXDOP overrides the server-wide setting, and the cost threshold for parallelism is still respected when overriding MAXDOP in this way.

Consider the following very simple query using data from the 2008 SR4 AdventureWorksDW sample database:

SELECT COUNT_BIG(*) FROM dbo.FactInternetSales;

On a server configured with Server MAXDOP = 1, cost threshold = 0, you get a simple serial plan as expected.

Adding OPTION (MAXDOP 2) to the query produces a parallel plan with an estimated plan cost of 0.17

Change the cost threshold for parallelism to 1 and we return to a serial plan.

Separately, Bob’s point was to not set server-wide MAXDOP to 1 in an attempt to fix CXPACKET waits. I could not agree more. My point was, and continues to be, that OLTP environments can benefit from server MAXDOP = 1, with the DBA selectively overriding MAXDOP as shown. That was why I heavily qualified my previous comment with the statement concerning OLTP workloads.

Nickname: manish
rating 4
rating 4


Cluster Upgrade (SQL Server 2008 R2–> SQL Server “Denali”)

SSIS clustering

SE Master

SELECT * FROM sys.dm_io_cluster_shared_drives

Meanwhile, the following system function has the same results in SQL Server 2000


Replication Merge



new enetered key WQ27D-PY77P-R9CQK-MCPPB- QGJYQ



Replication Merge



Gud topics or blogs
Deadlocks and trace flag 1204,d.bmk
trace flags


piecemeal-restore-improve-availability.aspx (Must),guid,03a7af66-b480-44c1-af26-262994351e2e.aspx

How to enable trace flags on MS cluster? in registry, in one node or every node in cluster

Add data file to logshipping
Adding a log shipping monitor

You can get the response time and the service time of a query by looking at sys.dm_exec_sessions,
columns total_elapsed_time and cpu_time

Integration Services Roles (SSIS Service)

Migrating SQL Reporting Services to a new server by moving the Reporting Services databases

Understanding SQL server memory grant

2 time
cinarest cold

ciprobid 250


Request No: 261610246


Transaction replication performance
How to Massively Optimise Transactional Replication
Can I write to a replicated database?
Understanding How Transaction are Applied in Transactional Replication in SQL Server 2005

Indexs not replicated
Restore of Replicated Database fails with “Cannot drop the table because it is being used for replication

Calculating Replication Schema Options
Published Database Transaction Log continues to Grow!(sync with backup)
Troubleshooting LogReader Timeout executing sp_replcmd
Lessons learned updating 100 millions rows
Add article
tracing delay

9350523619 shabir
Commithreshold is honored at a transaction boundary. Let’s take the
following example

Commitbatchsize 10
Commitbatchthreshold 20

Transaction1 has 19 commands
Transaction 2 has 11 commands
Transaction 3 has 5 commands

With the above setting, the distribution agent reader thread will set the
commit flag for the command block after we read all the commands in
transaction 2. This is because we check the commitbatch size and
commitbatchthreshold after a transaction boundary is reached. We dont issue
a commit after we read the first command (20th command in that batch) in
transaction 2. So this commit batch on the subscriber will have committed 2
transactions with 30 commands even though the threshold is 20.

So the batch will be committed either when we hit the commitbatchsize OR
when a transaction crosses the command threshold before the commitbatchsize
is hit.

With regard to the threshold adding one more command that seems to be a bug.

Download videos

Ghost records,recover sa password, forgot password of ssis package,login to sql server when sa lost, transactional replication lessons

SQL2K Side-By-Side with SQL2K5
Changing Service Account & Service Account Password

Gogula: try the MCTS for sql server
there are 3 differnet ones in the SQL Server 2008 track
70-433 is easy
me: can i complete for sql 2008 now?
only 1 is sufficient
Sent at 5:12 AM on Friday
Gogula: 1 is sufficient for MCTS
then you can do 70-451 to get the MCITP
if you want to do the easiest – those are the two
Sent at 5:20 AM on Friday
–Professional Microsoft SQL Server 2012 Administration free ebook

2 time
cinarest cold

ciprobid 250

The best of Site

Simple backup Strategy for Distribution database

DBCC MEMORYSTATUS : How is Stolen Potential calculated

=>How to create custom schedule for SQL Server Agent Job

Halloween Protection

Why does SQL Server not compress data on LOB pages?


The best of Site
Simple backup Strategy for Distribution database

DBCC MEMORYSTATUS : How is Stolen Potential calculated

WHEN mdh.runstatus = ‘1’ THEN ‘Start – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘2’ THEN ‘Succeed – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘3’ THEN ‘InProgress – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘4’ THEN ‘Idle – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘5’ THEN ‘Retry – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘6’ THEN ‘Fail – ‘+cast(mdh.runstatus as varchar)
ELSE CAST(mdh.runstatus AS VARCHAR)
END) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
right(left(,LEN(, LEN(left(,LEN( when mda.publisher_db=’ALL’ then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],
CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
und.UndelivCmdsInDistDB [UndistCom],
mdh.comments [Comments],
‘select * from distribution.dbo.msrepl_errors (nolock) where id = ‘ + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
WHEN mda.subscription_type = ‘0’ THEN ‘Push’
WHEN mda.subscription_type = ‘1’ THEN ‘Pull’
WHEN mda.subscription_type = ‘2’ THEN ‘Anonymous’
ELSE CAST(mda.subscription_type AS VARCHAR)
END) [SUB Type],

mda.publisher_db+’ – ‘+CAST(mda.publisher_database_id as varchar) [Publisher DB], [Pub – DB – Publication – SUB – AgentID]
FROM distribution.dbo.MSdistribution_agents mda
LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id =
(SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
FROM distribution.dbo.MSrepl_commands t (NOLOCK)
JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
(SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
FROM distribution.dbo.MSdistribution_history (NOLOCK)
GROUP BY agent_id) AS h
ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
GROUP BY hist.agent_id, h.maxseq
) AS MaxAgentValue
ON MaxAgentValue.agent_id = s.agent_id
GROUP BY s.agent_id, MaxAgentValue.[time]
) und
ON = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db’virtual’ — created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
–and mdh.runstatus=’6′ –Fail
–and mdh.runstatus’2′ –Succeed
order by mdh.[time]

Index Fragmentation

Great SQL Server Debates: Buffer Cache Hit Ratio

Contained database


SQL Server Maintenance Plans

9 Jan

SQL Server Maintenance Plans
The results generated by maintenance tasks in a maintenance plan can be written as a report to a text file, or written to the maintenance plan tables, sysmaintplan_log and sysmaintplan_logdetail, in msdb. To view the results in the log file viewer, right-click Maintenance Plans, and then click View History.

Note that maintenance plans only run against databases set to compatibility level 80 or higher. The maintenance plan designer in SQL Server Management Studio does not display databases set to compatibility level 70 or lower.

Important security considerations:  You must be a member of the sysadmin role to create and manage maintenance plans, and to view them in Object Explorer. The SQL Server Agent node in Object Explorer is only displayed for members of the sysadmin fixed server role, SQLAgentReaderRole, SQLAgentUserRole, or SQLAgentOperatorRole fixed database roles. Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles

Remember that statistics are not the same in SQL Server 2005 as they are in SQL Server 2000 and that they must be upgraded using the sp_updatestats stored procedure after you migrate from version 2000 to 20005.  Otherwise, the old statistics are unusable by the query engine.  Once you’ve run sp_updatestats, the engine will maintain them automatically.

important links sql Server

12 Nov

Evaluation edition to paid edition upgrade and trace flag sp_procoption for DBCC


Analysis services administration notes




SSIS over cluster


Concurrency Series: Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?

Restore over network

–It is possible to delete the data/log files in SQL Express Edition even if the Instance is UP

Storage Best practices

OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

Storage Modes in Analysis Services

Lock Escalation in SQL2005

Bulk Import Optimizations (Minimal Logging)

sunil agarwal

Memory pressure, Lock pages in memory

SAP on sql server

Quick Fix Engineering, CTPs, RTM,HotFixes,Service Packs,CUs Sql Server

Behavior of SQL Server default instance on a NON-Default port

What types of objects are useful in SQL CLR?

trans Replication sync with backup

sys.dm_db_index_physical stats

DBCC reindex and alter index

Using Bulk Logged recovery model for Bulk Operations will reduce the size of Transaction log backups – Myths and Truths

Checking for CPU pressure via sys.dm_os_schedulers

www.docseek.netHow-to & Guides