Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Logical Disk Read Bytes/sec and disk io

In the SAN environment , the Logical Disk Performance objects counters monitor the logical partitions.

 I use the Logical Disk Read Bytes/sec  as a starting point when I troubleshoot Disk IO issues. If I can isolate the activity , this gives me a good guideline as to Read rates from… Read more

0 comments, 775 reads

Posted in SQLServer-DBA on 12 January 2012

SQL Server – Backup to NUL trick - read throughput

A neat test for read speed backup throughput is to use the Backup to NUL .

NUL is a special “file”. It's as a nul device , anything  “written” to it is discarded.Don’t confuse with the SQL reserved word NULL.

Before you run this statement , note that even as the… Read more

0 comments, 394 reads

Posted in SQLServer-DBA on 11 January 2012

SQL Server – Maintaining different environments

A busy Database Server  and application development environment  can have – Sandbox, Dev, Test, QA, Staging, Production .

Maintaining multiple environments requires consideration of  various factors.

Reasons for maintaining different environments

Part of well audited database server system includes Segregation of Duty. Part of Segregation of duty is to define… Read more

0 comments, 1,285 reads

Posted in SQLServer-DBA on 10 January 2012

SQL Server –WRITELOG and how to reduce it

SQL Server Books online defines the wait type WRITELOG as “Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits. “

A checkpoint writes all SQL dirty pages , currently in the buffer,  onto disk. Transaction commits make  data modifications… Read more

0 comments, 375 reads

Posted in SQLServer-DBA on 9 January 2012

Arithmetic overflow error and isdate sql

Executing a t-sql statement with a convert function

 convert(datetime, my_date_column)            ,           

 threw an arithmetical overflow error with the message :

Message
 Arithmetic overflow error converting expression to data type datetime. [SQLSTATE 22003] (Error 8115)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

 

Use… Read more

0 comments, 571 reads

Posted in SQLServer-DBA on 6 January 2012

SQL Server Disable Indexes and Rebuild Indexes dynamically

To generate the ALTER INDEX..DISABLE  and  the ALTER INDEX..REBUILD statements for all nonclustered indexes for a single table use the following sql statements. 

Copy and Paste the statements , execute on the database. I’ve included the URL column  for easy click through to some notes on the code

1) Generate… Read more

0 comments, 541 reads

Posted in SQLServer-DBA on 5 January 2012

SQL SERVER REBUILD INDEX

Use the script below to rebuild an index on a table

ALTER INDEX   MY_INDEX_NAME on MY_TABLE_NAME  REBUILD  ; 

 Check SQL Server BOL for a the full list of options

 Notes

1)  Issuing an Index Rebuild request , drops and recreates the index

2) If  rebuilding a  CLUSTERED INDEX ,… Read more

0 comments, 469 reads

Posted in SQLServer-DBA on 4 January 2012

SQL SERVER DISABLE INDEX

Use the script below to disable an index on a SQL Server table

ALTER INDEX MY_INDEX_NAME on MY_TABLE_NAME  DISABLE; 

 

Executing the code will  prevent access to the index. 

If it’s a CLUSTERED INDEX , the data remains intact on the table , but no DML can access the data Read more

0 comments, 289 reads

Posted in SQLServer-DBA on 3 January 2012

SQL Index Fragmentation and sys.dm_db_index_physical_stats

Fragmentation of an index can severely affect performance. When logical ordering of the key within a page does not match the physical ordering within the data file, fragmentation exists.

I execute index maintenance scripts  for databases on a regular basis.  If I’m executing a  custom job, such as a large… Read more

0 comments, 513 reads

Posted in SQLServer-DBA on 2 January 2012

SQL Server DBA – Grow as a DBA

It’s the first day of 2012.  Setting New Year Resolutions is often a futile activity – and  resolutions I’ve set in the past tend to crumble as soon as I become busy again.

I prefer to emphasise the on going challenge of learning and developing as a DBA .

My… Read more

0 comments, 233 reads

Posted in SQLServer-DBA on 1 January 2012

SQLServerDBA News - 2011 Review for SQLServerDBA

2011 was a great year on a personal and professional level

1)       Completed the Brighton Marathon in 4hrs 36 secs

2)       Blogged on SQLServerDBA.com and DBA-DB2.com frequently

3)       More time spent on Database Architecture projects

4)       Read more books for recreation

5)       My favourite SQLServer-DBA.com posts this year were:

JOIN… Read more

0 comments, 652 reads

Posted in SQLServer-DBA on 31 December 2011

Routing OSPF and Network Team

After a server move to the failover Data Centre – a file copy between the two sites slowed down by 6x.  I ran the copy a few times to confirm the slow down.

The file is part of a SQL Server ETL process. The ETL process transforms the data and… Read more

0 comments, 273 reads

Posted in SQLServer-DBA on 21 December 2011

Nic Teaming

Network card teaming or NIC teaming are networking terms describing methods of combining multiple parallel network connections . The main purposes are 1) increasing throughput beyond a single connection and 2)  redundancy.

 The three main teaming configurations

 Network Fault Tolerance (NFT)

1)  Two – Eight Physical NICS are teamed… Read more

0 comments, 293 reads

Posted in SQLServer-DBA on 14 December 2011

CPU Count

I document the CPU Count for SQL Server inventory management.

Using a powershell script , iterate through all the SQL Servers and capture the CPU count with the following t-sql.

----Returns the logical cpu count on server 
SELECT cpu_count FROM  sys.dm_os_sys_info

----Ratio of the number of logical or physical cores…

Read more

0 comments, 393 reads

Posted in SQLServer-DBA on 13 December 2011

SQL Traces and c2 auditing are different

Sometimes end users interchange the terms : C2 auditing and Traces.
They server different purposes.

c2 auditing records all attempts (successfull and failed) on objects and statements. Typically a SQL Server DBA will use a c2 audit to monitor security compliance .

A trace will will monitor an event or… Read more

0 comments, 393 reads

Posted in SQLServer-DBA on 12 December 2011

SQL Database Status with sys.databases

I was creating a Powershell script for a SQL Server checkout procedure after a reboot , and used  the state_desc column in sys.databases view to report on the database status.

The descriptions , courtesy of BOL are:

ONLINE  - Database is available for access

RESTORING – One or more… Read more

0 comments, 341 reads

Posted in SQLServer-DBA on 9 December 2011

SQL Server Latency

Latency is the time delay measure in the system.  SQL Server  can only be as fast as the slowest component in the system.

Database servers are particularly sensitive to disk IO speeds. For example, introducing an SVC system may speed up writes , but will add a cost to Reads. Read more

0 comments, 652 reads

Posted in SQLServer-DBA on 7 December 2011

DATEADD and variables in SQL

To make the T-SQL  function DATEADD more dynamic use variables. The example below , accepts an input parameter , in this case “4”.

All events older than 4 months from now will be deleted.

 

DECLARE @months int
SET @months = 4

DELETE FROM [Events] WHERE [EventDate] < DATEADD (month,-1*@months,…

Read more

0 comments, 569 reads

Posted in SQLServer-DBA on 6 December 2011

Enable XA transactions for SQL Server

An XA transaction is a  global transaction usually covering multiple resources.

Use  the  Microsoft SQL Server JDBC Driver to support the   Java Platform based distributed transactions

Steps to enable XA transactions

1)  Enable MSDTC

On Windows 2003 > Start > Run > “dcomconfg” > Expand Component Services > Expand Computers… Read more

0 comments, 3,013 reads

Posted in SQLServer-DBA on 5 December 2011

Newer posts

Older posts