SQL Server 2012 Service Pack 1(SP1) Customer Technology Preview 4 (CTP4) Available

MICROSOFT® SQL SERVER® 2012 SERVICE PACK 1 (SP1), COMMUNITY TECHNOLOGY PREVIEW 4 (CTP4)

Download:
SQL Server 2012 SP1 CTP4
SQL Server 2012 SP1 CTP4 Express
SQL Server 2012 SP1 CTP4 Feature Pack

The SQL Server 2012 builds that were released after SQL Server 2012 was released:
Download: http://support.microsoft.com/kb/2692828/

Full story »

Antivirus Software on SQL Server

It’s an old story, but still present - Why or why not installing antivirus software on a database server?

First of all, I’ve been on both sides of the debate with respect, but I can only, only partially agree to install antivirus software on a database server - e.g Microsoft SQL Server. And this only partially agreement means, first not to install antivirus software on SQL Server as many IT departments do in a standard procedure. In addition, the default settings are left untouched, so that every virtually byte is examined in real time.

Have you ever examined the amount of memory, high CPU spikes or high CPU usage and last but not least your high consumer of I/O activities on your (busy) SQL Server?
So let us take a closer look by using Process Explorer to identify antivirus and SQL Server tasks since start-up.

 

Identifying the SQL Server and Antivirus consumers

I prefer using Process Explorer to take a immediate first look what happens on the server, he includes many interesting counters for regarding some performance issues and more. By selecting View-Select Columns…, we can add interesting counters such as Private Bytes, Working Set, Threads, Handles, I/O Reads, I/O Read Bytes, I/O Writes, I/O Write Bytes, CPU Time – as shown in the screenshot below.

Process Explorer Private Bytes, Working Set, Threads, Handles, I/O Reads, I/O Read Bytes, I/O Writes, I/O Write Bytes, CPU Time

The screenshot above shows that while the ntrtscan.exe (Real-time Scan Service) has read 70% of the bytes of SQL Server, it has performed 27 times as much read I/O as the read activity isn’t as efficient as SQL Server. And the tmlisten.exe (antivirus communication service) has only write 7% of the bytes of SQL Server, but it has performed four times as much write I/O as the write activity of SQL Server.

Now I want to see the leaders of high I/O Reads on my server by filtering I/O Reads (see screenshot below).

Process Explorer - Filter high I/O Reads

The first three consumers of I/O Reads are:

  1. Antivirus (ntrtscan) with 111.641.100 Reads
  2. SQL Server (sqlservr) with 4.622.225 Reads
  3. Antivirus (tmlisten) with 2409809 Reads

Same doing on I/O Writes:

Process Explorer - Filter high I/O Writes
  1. Antivirus (tmlisten) with 31.124.046 Writes
  2. SQL Server (sqlservr) with 9.785.283 Writes
  3. ReportingServices with 1.715.489 Writes

So, it’s possible for antivirus to consume many times of I/O’s of SQL Server which can really hamper performance on your (busy) SQL Server. It is possible that the antivirus is consuming more than 50% of total I/O activities! (Please note the recommendations to Microsoft SQL Server Database Engine Input/Output Requirements)

 

And what does Microsoft say for antivirus on your SQL Server (KB309422)?



"We strongly recommend that you individually assess the security risk for each computer that is running SQL Server in your environment and that you select the tools that are appropriate for the security risk level of each computer that is running SQL Server. Additionally, we recommend that before you roll out any virus-protection project, you test the whole system under a full load to measure any changes in stability and performance.
Virus protection software requires some system resources to execute. You must perform testing before and after you install your antivirus software to determine whether there is any performance effect on the computer that is running SQL Server.
"

In my opinion it is very carefully worded, with a “It depends on …” – but they help us!

Read this KB very carefully with you Antivirus Administrator, talk with him about virus risks on the server, performance effects on a (busy) SQL Server and how intrusive the antivirus is at the ratio of the security risk level. In addition read also KB822158, there are further recommendations for the windows server and should be applied as well and first!

I don’t want to repeat these KBs, but here are some recommended must reads:

 


 Turn off scanning of Windows Update or Automatic Update related files
 Turn off scanning of Windows Security files
 Turn off scanning of Group Policy related files
 Directories and file-name extensions to exclude from virus scanning
 Processes to exclude from virus scanning
 Considerations for clustering

AND - consider an external virus scan against your SQL Server, which is possible in most enterprise antivirus products – often you needn’t a real time virus scan, it belongs to the security risk level!

I wish you a nice  day,
tosc

Torsten Schuessler

SQL Server 2012 - New and Modified Dynamic Management Views and Functions - DMVs

 

That's just about the limit. In Books Online for SQL Server 2012 (BOL) by reading What's New (Database Engine), I came around the topic Manageability Enhancements (Database Engine) and its paragraph New and Modified Dynamic Management Views and Functions, and I only see six added or modified system views. And I think by myself, these are the same six DMVs I had read in CTP Denali BOL - no update?

But I knew that there are more new DMVs. I do not mean the AlwaysOn Availability Groups Dynamic Management Views and Functions of the AlwaysOn feature etc. ... - So, I think by myself - figure out!

OK, I have a SQL Server 2008 R2 instance and a SQL Server 2012 instance (both are only for testing, no production environment!). But I don't want to query each instance and copy the result for further investigations in excel - like this way:

USE [master];
GO
SELECT name, type, type_desc
FROM sys.system_objects  WITH (NOLOCK)
WHERE name LIKE 'dm_%'
ORDER BY name OPTION (RECOMPILE);
GO

 

New Dynamic Management Views and Functions in SQL Server 2012

I create a valid linked server point form SQL Server 2012 to SQL Server 2008 R2 (@server = N'SQL2008R2'), then I run below query from SQL Server 2012 - which will find DMVs that did not exists in 2008 R2 ('SQL2008R2') -  comparing the datasets in sys.system_objects.

 

USE [master];  --- execute on SQL Server 2012
GO
SELECT
       N'sys.' d.[name] AS [DMV NAME],
       d.[type] AS [DMV TYPE],
       d.type_desc AS [DMV TYPE DESC]
FROM
      [master].sys.system_objects AS WITH (NOLOCK)
LEFT OUTER JOIN --- LinkedServer SQL Server 2008 R2
      [SQL2008R2].[master].sys.system_objects AS WITH (NOLOCK)
ON
      d.[name] r.[name] ---* collate Latin1_General_CI_AS
      AND d.[schema_id] = r.[schema_id]
WHERE
      r.[name] IS NULL
      AND d.[schema_id] = 4
      AND d.[is_ms_shipped] = 1
      AND d.[name] like 'dm_%'
ORDER BY
      d.[name] OPTION (RECOMPILE);
GO

Linked Query Result

I get this resultset, and YES SQL Server 2012 has really 36 new Dynamic Management Views and Functions.

 
DMV NAMEDMV TYPEDMV TYPE DESC
sys.dm_db_database_page_allocations IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_fts_index_physical_stats VIEW
sys.dm_db_log_space_usage VIEW
sys.dm_db_objects_disabled_on_compatibility_level_change IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_uncontained_entities VIEW
sys.dm_exec_describe_first_result_set IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_exec_describe_first_result_set_for_object IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_filestream_non_transacted_handles VIEW
sys.dm_fts_index_keywords_by_property IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_fts_semantic_similarity_population VIEW
sys.dm_hadr_auto_page_repair VIEW
sys.dm_hadr_availability_group_states VIEW
sys.dm_hadr_availability_replica_cluster_nodes VIEW
sys.dm_hadr_availability_replica_cluster_states VIEW
sys.dm_hadr_availability_replica_states VIEW
sys.dm_hadr_cluster VIEW
sys.dm_hadr_cluster_members VIEW
sys.dm_hadr_cluster_networks VIEW
sys.dm_hadr_database_replica_cluster_states VIEW
sys.dm_hadr_database_replica_states VIEW
sys.dm_hadr_instance_node_map VIEW
sys.dm_hadr_name_id_map VIEW
sys.dm_logconsumer_cachebufferrefs IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logconsumer_privatecachebuffers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_consumers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_hashentries VIEW
sys.dm_logpool_sharedcachebuffers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_stats VIEW
sys.dm_logpoolmgr_freepools IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_respoolsize IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_stats IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_os_cluster_properties VIEW
sys.dm_os_memory_broker_clerks VIEW
sys.dm_os_server_diagnostics_log_configurations VIEW
sys.dm_resource_governor_resource_pool_affinity VIEW
sys.dm_tcp_listener_states VIEW

... Now you can query Books Online for SQL Server 2012 ....

To struggle through these new Dynamic Management Views and Functions in SQL Server 2012, will be an ongoing and hard work :-)

I wish you a nice  day,
tosc

Torsten Schuessler

New Version of SQL Server Maintenance Solution by Ola Hallengren Now Available

Ola Hallengren and his ongoing work of his SQL Server Maintenance Solution - SQL Server Backup, Integrity Check, and Index and Statistics Maintenance.The solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs.

Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 2011 and 2010 SQL Server Magazine Awards. I love this tool to perform index maintenance on instances with VLDBs - and the best it's free!

New Version Available

A new version of the SQL Server Maintenance Solution is available. You can now perform striped backups across multiple drives in the DatabaseBackup stored procedure. Simply specify multiple directories in the @Directory parameter; for example:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup, D:\Backup, E:\Backup, F:\Backup', @BackupType = 'FULL',
@Compress = 'Y',
@NumberOfFiles = 4

The new version also supports checking the consistency of a database on the file group level and on the table level. This capability is useful when you have VLDBs.

You can read more about the most recent version of the solution at http://ola.hallengren.com/versions.html or download it at http://ola.hallengren.com/scripts/MaintenanceSolution.sql

 

And now it's up to YOU!

2012 SQL Server Pro Community Choice Awards

The voting for the 2012 SQL Server Pro Community Choice Awards is now open! This year, the Ola Hallengren SQL Server Maintenance Solution has been nominated in the Best Backup & Recovery Product and Best Free Tool categories. So let us VOTE!!!

Please feel free to contact him if you have any questions. And I tell you, Ola will consider it!!!

I wish you a nice  day,
tosc

SQLU Summit 2012 in BRATISLAVA

I know that finding time to attend business events is becoming more and more difficult, but there is a great SQL Server Conference in Bratislava.

Enjoy the lovely capital of Slovakia in the heart of Europe! From September 19th to 21st a selected group of top-speakers from the SQL Server Community is heading in to give you deep insight into SQL Server 2012 in many different aspects . Exchange with experts and colleagues about the possible deployments of these new technologies in practice!

Intensive three-day workshops, keynote by Lubor Kollar with insights into what is currently the SQL Server kitchen simmers and 27 exciting three-day sessions result in a sustained learning experience.

Who's who SQL Speakers:

    • Lubor Kollar - Principal Program Manager in SQL Server Engine Development Team
    • Itzik Ben-Gan – globally renown T-SQL guru, author and trainer (T-SQL Champion)
    • Thomas Kejser - Principal Program Manager at Microsoft. Performing fast loading of warehousing! (removing every bottleneck)
    • Dejan Sarka – Data Mining guru
    • Davide Mauri – BI expert, awards for best-rated session
    • Alexei Khalyako - SQL Regional Program Manager at SQL Server International Customer Advisory Team
    • Karol Papaj - SQL Server MVP and local hero
    • Microsoft SQL Server Development Team from Belgrade

Early Bird until 15. August: € 834.00 (excl. VAT) 
AS a member of PASS Germany until 15. August: € 750.60 (excl. VAT)

Remember as a member of PASS Deutschland e.V you get a very special discount - so become a member of the great German Microsoft SQL Server Community - http://www.sqlpass.de

I wish you a nice day,
tosc

Service Pack 2 (SP2) is available for SQL Server 2008 R2

SQL Server R2 Service Pack 2 (SP2 Build 10.50.4000.0) is available:

http://www.microsoft.com/en-us/download/details.aspx?id=30437

 

List of the bugs that are fixed in SQL Server 2008 R2 Service Pack 2
http://support.microsoft.com/kb/2630458

I wish you a nice day,
tosc

 

Torsten Schuessler

SQL Server 2012 Service Pack 1(SP1) Customer Technology Preview 3 (CTP3) Available

MICROSOFT® SQL SERVER® 2012 SERVICE PACK 1 (SP1), COMMUNITY TECHNOLOGY PREVIEW 3 (CTP3)
 
The SQL Server 2012 builds that were released after SQL Server 2012 was released:
 

Full story »

Database Engine Service Startup Options in SQL Server 2012

Before SQL Server 2012, it was a difficult work to add a trace flag or a startup parameter other than the default startup parameters that SQL Server uses. And, by the way, how the paramater is added from SSCM - the registry keys may not be created correctly - resulting in SQL Server startup code skipping these parameters!!!
In those days, I copy my defaults to Notepad++, than I add paramaters in this way:
  1. Add any new parameters after the first 3 default parameters –d, -e, -l. IMPORTANT never change sorting of these parameters!
  2. Specify the ";" delimiter between each parameter! SSCM uses ";" as the delimiter between parameters.
  3. Do NOT use a space after the delimiter and the parameter identifier (which is the "-" character).

    Copy it back to startup paramaters - save - RESTART the service!

  4. Check the current SQL Server error log and verify that each parameter shows up in a new line and starts at the same indentation.

But now in SQL Server 2012 we have a "Startup Parameters" Tab!

  1. Add any new parameters in the Textfield (NEVER REMOVE the first 3 default pramaters!!!)



  2. After restarting check the current SQL Server error log and verify that each parameter shows up in a new line and starts at the same indentation.

Where to find out about trace flags

Checking the status of trace flags

You can check the status of your trace flags by using the following command:
DBCC TRACESTATUS

 

If you want to know something about Trace Flag 3226, please read Christoph Muthmann's (aka cmu) post:
Trace Flag 3226

 
I wish you a nice day,
tosc

 

Torsten Schuessler

#CU package 2 for SQL Server 2012

The Cumulative update package 2 for SQL Server 2012 (Build 11.0.2325.0) is available:

Cumulative update package 2 for SQL Server 2012 is available

And here you can get the whole information of builds that were released after SQL Server 2012 was released:

http://support.microsoft.com/kb/2692828/

 

I wish you a nice day,
tosc

 

Torsten Schuessler

CUs available for SQL Server 2008 R2

The Cumulative update package 14 for SQL Server 2008 R2 (Build 10.50.1817.0) is available:

Cumulative update package 14 for SQL Server 2008 R2

And here you can get the whole information of builds that were released after SQL Server 2008 R2 was released:

http://support.microsoft.com/kb/981356/

 
 

The Cumulative update package 7 for SQL Server 2008 R2 Service Pack 1(Build 10.50.2817.0) is available:

Cumulative update package 7 for SQL Server 2008 R2 Service Pack 1

And here you can get the whole information of builds that were released after SQL Server 2008 R2 Service Pac k 1 was released:

http://support.microsoft.com/kb/981356/

I wish you a nice day,
tosc

 

Torsten Schuessler
« 1 2 3 4 5 6 7 8 9 10 11 ... 25 »