SQL Server Sizing, HA and Performance hints

SQL Server 2012 customize SQL Server Management Studio (SSMS)

Matthias Sessler — Tue, 23 Apr 2013 12:13:41 GMT

There are several Options that can be modified inside SSMS. At example you can adjust "edit top 200 rows" to any number that you would like to use under Tools->Options->SQL Server Object Explorer->"Value for Edit Top<n> Rows command".

A complete overview about the Options, Features, e.g. that you can configure you'll find at: http://technet.microsoft.com/en-us/library/ms174173.aspx and the corresponding subcategories.

SQL Server 2012 Performance Counter und DMV Änderungen im Vergleich zu SQL 2008 durch neues SQLOS

Matthias Sessler — Wed, 13 Feb 2013 08:34:36 GMT

Durch die Einführung des neuen SQLOS mit SQL 2012 ergeben sich nachfolgend soweit aufgefallen folgende neuen Counter in den System DMW's und Performance Monitor:

sys.dm_os_memory_nodes


SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
foreign_committed_kb	-

“foreign_commited_kb zeigt jetzt die Werte von remotememory (anderer Numa Nodes) an.

sys.dm_os_sys_info


SQL Server 2012	SQL Server 2008 R2
physical_memory_kb	physical_memory_in_bytes
virtual_memory_kb	virtual_memory_in_bytes
committed_kb	bpool_committed
committed_target_kb	bpool_commit_target
visible_target_kb	bpool_visible

sys.dm_os_memory_cache_counters


SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
pages_in_use_kb	single_pages_in_use_kb + multi_pages_in_use_kb

sys.dm_os_memory_cache_entries


SQL Server 2012	SQL Server 2008 R2
pages_kb	pages_allocated_count

sys.dm_os_memory_clerks


SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
page_size_in_bytes	page_size_bytes

sys.dm_os_memory_objects


SQL Server 2012	SQL Server 2008 R2
pages_in_bytes	pages_allocated_count
max_pages_in_bytes	max_pages_allocated_count

DBCC Memory Status changes

SQL Server 2012	SQL Server 2008 R2
Memory Manager \ Large Pages Allocated	-
Memory Manager \ Emergency Memory	Memory Manager \ Reserved Memory
Memory Manager \ Emergency Memory In Use	Memory Manager \ Reserved Memory In Use
Memory Manager \ Target Committed (KB)	Buffer Pool \ Target (Pages)
Memory Manager \ Current Committed (KB)	Buffer Pool \ Committed (Pages)
Memory Manager \ Pages Allocated (KB)	Buffer Pool \ Stolen (Pages) + Buffer Pool \ Database (Pages)
Memory Manager \ Pages Reserved (KB)	Buffer Pool \ Reserved (Pages)
Memory Manager \ Pages Free (KB)	Buffer Pool \ Free (Pages)
Memory Manager \ Pages In Use	-
Memory Manager \ Page Alloc Potential (KB)	Buffer Pool \ Stolen Potential (Pages)
Memory Manager \ NUMA Growth Phase	-
Memory Manager \ Last OOM Factor	Buffer Pool \ Last OOM Factor
Memory Manager \ Last OS Error	-

Memory Manager \ Large Pages Allocated	-
Memory Manager \ Emergency Memory	Memory Manager \ Reserved Memory
Memory Manager \ Emergency Memory In Use	Memory Manager \ Reserved Memory In Use
Memory Manager \ Target Committed (KB)	Buffer Pool \ Target (Pages)
Memory Manager \ Current Committed (KB)	Buffer Pool \ Committed (Pages)
Memory Manager \ Pages Allocated (KB)	Buffer Pool \ Stolen (Pages) + Buffer Pool \ Database (Pages)
Memory Manager \ Pages Reserved (KB)	Buffer Pool \ Reserved (Pages)
Memory Manager \ Pages Free (KB)	Buffer Pool \ Free (Pages)
Memory Manager \ Pages In Use	-
Memory Manager \ Page Alloc Potential (KB)	Buffer Pool \ Stolen Potential (Pages)
Memory Manager \ NUMA Growth Phase	-
Memory Manager \ Last OOM Factor	Buffer Pool \ Last OOM Factor
Memory Manager \ Last OS Error	-

Memory node Id = n \ Pages Allocated	Memory node Id = n \ MultiPage Allocator + Memory node Id = n \ SinglePage Allocator
Memory node Id = n \ Target Committed	-
Memory node Id = n \ Current Committed	-
Memory node Id = n \ Foreign Committed	-
Memory node Id = n \ Away Committed	-
Memory Clerks \ Pages Allocated	Memory Clerks \ SinglePage Allocator + Memory Clerks \ MultiPage Allocator

“Away Commited“ Counter zeigen wieviel des Speichers von RemoteNodes genutzt wird.

Performance monitor counters

SQL Server 2012	SQL Server 2008 R2
Object - SQLServer:Memory Manager	Object - SQLServer:Buffer Manager
Database Cache Memory (KB)	Database pages
Free Memory (KB)	Free pages
Reserved Server Memory (KB)	Reserved pages
Stolen Server Memory (KB)	Stolen pages
Target Server Memory (KB)	Target pages
Total Server Memory (KB)	Total pages
-	AWE lookup maps / sec
-	AWE stolen maps / sec
-	AWE write maps / sec
-	AWE unmap calls / sec
-	AWE unmap pages / sec

Object - SQLServer:Memory Node		Object - SQLServer:Buffer Node
Database Node Memory (KB)		Database pages
Free Node Memory (KB)		Free pages
Foreign Node Memory (KB)		Foreign pages
Stolen Node Memory (KB)		Stolen pages
Target Node Memory (KB)		Target pages
Total Node Memory (KB)		Total page

SQL Server 2012 Enterprise Edition uses only 20 Cores - es werden nur 20 Cores genutzt

Matthias Sessler — Tue, 22 Jan 2013 08:17:42 GMT

Recently we discovered that some of the customers SQL 2012 Enterprise Edition Installations only uses 20 Cores (Machine has 64 Cores). After some Research we discovered, that there was a SQL 2012 Enterpriese Edition CAL License (PID/SKU) used. So to upgrade you can use SQL Server Setup to Change the product key to the SQL Core license key. If you are using Availability Group Function there might be warnings, but after upgrade our Environment with AVG works as before and now uses the 64 Cores.

More Informations could be found on the Blog: http://blogs.msdn.com/b/saponsqlserver/archive/2012/06/15/sql-server-2012-enterprise-editions.aspx

Supported Upgrade Szenario's you'll find on: http://msdn.microsoft.com/en-us/library/ms143393.aspx

SQL 2012 - Availability Group Backup Transaction Log on Secondary shrink Transaction log fails

Matthias Sessler — Mon, 17 Dec 2012 15:19:32 GMT

Had some issues shrinking Transaction log. I thought this might be affected by availability Group function and taking your log backups on a secondary. But it seems that shrinking the Transaction log (dbcc shrinkfile) is not a Problem (you have to do it on the Primary for sure). Checking dbcc loginfo it Shows that there are some VLF (Virutal Log Files) in use (Status=2) but it wasn't the last VLF checking free space with" dbcc sqlperf (logspace)" also Shows plenty of room to shrink. dbcc opentran also Shows no Transaction that is blocking. So changing recovery model to simple is not an Option in AVG. I did a logbackup on the secondary and then shrinking the t-log was successfull. So if shrink Fails you may be at the end of the Log (VLF) and the only remedy is to do a backup. There is a lot of stuff around dbcc loginfo so I wouldn't write anything obout that you can search for it in the web there is plenty of stuff about. One I should mention is you also can have a look on what is going on about your log with: select log_reuse_wait, log_reuse_wait _desc from sys.databases .

cheers,

Matthias

SQL 2012 Availability Group on Fusion IO Drives Performance Explosion SSD Solid State Disk

Matthias Sessler — Tue, 11 Dec 2012 11:00:00 GMT

We did a customer implementation for a Sharepointfarm 2010. Goal was to reach high throughput and High Availability over 3 datacenter.

This is the part IO Testing and Results that we get from 2xFusion IO Duo (640GB) with Softwarestripe Windows 2008R2 on a DL 580 G7 4x16Core AMD, 192 GB RAM

Detailed Information you will find on the attached Excelspreadsheet (SQLIO testresults) done with 64K stripe and 64k ClusterNTFS Size.

We get about 3GB Random Read and about 2,2 GB Random Write out of the System. Latency at this Level is less than 20ms with 256 outstanding IO's writing randomly with 4 threads into 4 files each 8 GB in size.

SQL 2012 Availability Group HA Failover settings Cluster if Availability Group hangs in the Status of resolving

Matthias Sessler — Tue, 11 Dec 2012 10:50:32 GMT

Using Availability Groups you have to be Aware of Windows Failovercluster Settings. Because the Default Settings for Failover in a periodic time is 2 in 6 hours - sometimes you might observe the availability Group in Failovercases as Status "Resolving" wondering why you can't get the resource over to the secondary System changing to Primary this might depend on the fact that you already did some Failovers due to configuration tests or anything else especially in the stage of implementation or configuration. So go to the Clusteradministrator console (cluadmin.msc) and check your Availability Group Failoversetting (Role). You can Change the time period or the number of allowed Failover to whatever makes sense to you.

SQL Server 2012 Availability Group on Fusion IO Duo 3 GByte data throughput

Matthias Sessler — Mon, 29 Oct 2012 10:40:00 GMT

Aktuelle Tests beim Kunden zeigen hervorragende Ergebnisse mit Hilfe von Fusion IO Duo Accelerator Storage. Zum Einsatz kam eine DL 580 G7 mit 192 GB RAM und 64 Cores (AMD) mit 2 Fusion IO Duo Karten a 640 GB. Raidkonfiguration erfolgt mithilfe der Windows Raid 0 Funktion auf Basis Dynamischer Datenträger. Full Stripe wurde auf 64 K für SQL 2012 relationale Datenhaltung konfiguriert. Jede Fusion IO Duo Karte beinhaltet zwei Speichereinheiten auf MLC Basis a 320 GB (Sectorsize 512 Byte). Aufgrund der guten Lebensdauer und der hervorragenden Monitorings verzichten wir auf den Einsatz eines redundanten RAID Level. Hinzu kommt, dass die Systeme über 3 Rechenzentren mit Hilfe der Availability Group Funktion redundant (Synchron mit automatic Failover) eingesetzt werden.

Hier der erste Test mit SQLIO auf den Systemen (Datendurchsatz hatte sich nochmal verbessert, durch Erhöhung der Spannung von 25W auf einen höheren Wert auf dem PCIXpress Bus):

Test ist mit 4 Files je 8 GB und 4 Threads:

Random Read bei 64 K und 128 Outstanding IO's lag bei ca. 3 GByte Datendurchsatz pro Sekunde und einer durchschnittlichen Latency zwischen 12 ms.

Random Write bei 64 K und 128 Outstanding IO's lag bei ca. 2,1 GByte Datendurchsatz pro Sekunde und einer durchschnittlichen Latency von ca. 14 ms

Im Beitragsanhang die Auswertung als Exceldatei für die detailinteressierten.

How to upgrade to SQL Server 2012 Technical Reference Guide

Matthias Sessler — Mon, 21 May 2012 07:58:19 GMT

Es ist ein neues Upgrade Dokument erschienen. Dieses kann unter folgendem Link heruntergeladen werden: http://download.microsoft.com/download/9/5/3/9533501A-6F3E-4D03-A6A3-359AF6A79877/SQL_Server_2012_Upgrade_Technical_Reference_Guide_White_Paper.pdf

SQL Server 2008 R2 Error message "Invalid Product Key" during setup localized versions

Matthias Sessler — Tue, 17 Apr 2012 12:23:00 GMT

Issue:

Setup failure SQL Server 2008 R2 with the following error message “Invalid Product Key” after specifying a
valid product key is caused by specific language regional settings of the German Operating System. In detail concerns this the format of the “Region and
Language” settings in Control Panel. When the format is set to ‘Deutsch (Österreich)’ or ‘Deutsch (Schweiz)’ setup is failing with the error.

At that stage the SQL Server setup expects that the “Region and Language” would have the format ‘Deutsch (Deutsch)’.

Solution:

Installation runs successfully by changing the format in the Control Panel
->“Region and Language” settings before the installation to ‘Deutsch
(Deutsch)’. After successful installation you can set format back .

In Books Online of SQL Server documented you find the supportrelations between OS and SQL Server language Version.

http://msdn.microsoft.com/en-us/library/ee210665(v=SQL.105).aspx

Snip:

Cross-Language Support

The English-language version of SQL Server is supported on all localized versions of operating systems.
Localized versions of SQL Server are supported on localized operating systems with the corresponding language or on English-language versions of supported operating systems by using the Windows Multilingual User Interface Pack (MUI) settings. For more information, see How to: Change Operating System Settings to Support Localized Versions.
Localized versions of SQL Server can only be upgraded to localized versions of the same language, and cannot be upgraded to the English-language version.
Localized versions of SQL Server can also be installed side by side with English-language instances of SQL Server.

Lock pages in Memory - Sperren von Seiten im Speicher Windows X64 SQL X64 Trace Flag 834

Matthias Sessler — Thu, 12 Apr 2012 08:33:00 GMT

Immer wieder stellt sich die Frage ob das Recht Sperren von Seiten im Speicher für das SQL Service Konto auf X64 Systemen dieses Recht benötigt. Von Haus aus sind die X64 Versionen selbst in der Lage und benötigen keine zusätzlichen Rechte. Es gibt allerdings auch Ausnahmesituationen, in denen das Sezten dieses Rechtes sinnvoll sein kann. Informationen hierzu findet man im nachfolgenden Knowledghe Base Aritkel: http://support.microsoft.com/kb/918483

Traceflag 834 in diesem Zusammenhang:

Das Ablaufverfolgungsflag 834 bewirkt, dass SQL Server große Seitenreservierungen für den Speicherverwendet, die dem Pufferpool zugewiesen sind. Die Größe der Seiten hängt von der Hardwareplattform ab. Die Größe der Seite reichen möglicherweise von 2 MB bis 16 MB. Beim SQL Server Start werden diese zugewiesen und werden während der gesamten Lebensdauer des Prozesses beibehalten. Weitere Info's und Optimierungsmöglichkeiten im Knowledge Base Artikel: http://support.microsoft.com/kb/920093