I'm very proud to announce today that I'm running next year a new event series across Europe: the SQL Server 2012 Deep Dive Days. You can learn in this 3 day event all about the new things in SQL Server 2012. The event series is currently planned for the following cities around Europe:
- March 3 – 5, 2012 in Vienna/Austria
- March 12 – 14, 2012 in Munich/Germany
- March 19 – 21, 2012 in Zürich/Switzerland
- April 16 – 18, 2012 in Cologne/Germany
You can find further detailed information at http://www.sqlpassion.at/events.html where you can also register for the various locations. If you are not from the German speaking area of Europe, I'm also planning 2 events in English in May 2012 at the following locations:
- Brussels/Belgium
- London/United Kingdom
In this 3 days long event I'm covering the following topics about SQL Server 2012:
Day 1: SQL Server 2012 Development
- Project Juneau
- T-SQL Enhancements
- Spatial Data Types
- File Table
- Fulltext Indexes
- Semantic Search
- Service Broker
- SQL Express Edition - LocalDB
- Contained Databases
- Metadata Discovery
- SQL Server Integration Services
Day 2: SQL Server 2012 Administration
- Extended Events
- Columnstore Indexes
- LOB Online Index Rebuilds
- Adding Online Non-NULL Values
- Custom Server Roles
- Resource Governor
- Database Recovery Advisor
- Distributed Replay
- Memory Management
- Partitioned Tables
- Troubleshooting Enhancements
- Windows Server Core Support
- Windows Server 8 & SQL Server 2012
Day 3: SQL Server 2012 High Availability
If you are interested in attending one of these events, don't hesitate and register at http://www.sqlpassion.at/events.html. There is also an Early-Bird price available until the end of this year.
BTW: if you have missed the news, Microsoft released a few hours ago the RC0 version of SQL Server 2012 – you can download the bits here: http://www.microsoft.com/download/en/details.aspx?id=28145
Thanks for reading!
-Klaus
Today I want to talk about a phenomenon regarding memory management in SQL Server. A few weeks ago I had a consulting customer where we made some SQL Server performance improvements. As a side note the customer asked me, why SQL Server isn't using as much memory as possible on their production system. Their database was about 500 GB, and when we looked into the Task Manager of Windows, we saw that SQL Server was just consuming a few hundred MBs:
In the first step this behavior of SQL Server was a little bit amazing, but the answer was found very fast, when we looked into the details of their production configuration. The problem was that the customer granted the Locked Pages in Memory privilege to the service account under which SQL Server was running. You can check if you have granted this privilege when you look into the current SQL Server Error Log. If you have granted that privilege, you can see following informational message during the startup of SQL Server:
In the first step of this posting I want to explain why and when you should use that privilege for SQL Server, and in the second step you will see, how SQL Server uses that privilege and why Task Manager is lying to you regarding the memory consumption.
Every time when the Windows OS gets into memory pressure, the Windows OS raises a so-called Memory Resource Notification Event to all processes that are currently running on the box (see http://msdn.microsoft.com/en-us/library/windows/desktop/aa366541(v=vs.85).aspx for the corresponding API function). SQL Server subscribes to this event and processes it internally through a component called the Resource Monitor. Additional information about the Resource Monitor component is reported through the Ring Buffer type RING_BUFFER_RESOURCE_MONITOR that is stored inside the DMV sys.dm_os_ring_buffers. When SQL Server receives the Memory Resource Notification Event, SQL Server must trim its internal caches, like the Buffer Pool, or the Plan Cache to get out of memory pressure. But there are some scenarios in which SQL Server reacts to slow to memory pressure. In that case the Windows OS will do a so-called Hard Working Set Trim, and pages the process (in our case SQL Server) out to the page file. The Windows OS just want to survive and get out of its memory pressure. Imagine that – the Buffer Pool, the Plan Cache – In-Memory buffers – are paged out to the page file! Accessing the Buffer Pool – our in-memory cache - means reading pages from the physical disk! You can imagine that the performance of SQL Server will decrease massively…
Another case when a Hard Working Set Trim can occur is, when you have device drivers that have bugs, or when the Windows OS has bugs that leads to Hard Working Set Trims. Under Windows Server 2003 there were several bugs reported that caused Hard Working Set Trims, like:
- Copying large files from the SQL Server box
- Establishing a Remote Desktop Connection to your SQL Server box
But how you can find out if your performance problems occur because of Hard Working Set Trims? When you are running a version of SQL Server prior to SQL Server 2005 SP2, you have to monitor the overall performance of your SQL Server instance, there are no messages or indications inside SQL Server that can tell you, if a Hard Working Set Trim degraded the performance of SQL Server. Beginning with SQL Server 2005 SP2 Microsoft has added an error message to the SQL Server Log, as soon as a Hard Working Set Trim occurred, like:
" A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%"
In such cases you can enable the Locked Pages in Memory privilege to get rid of this issue. In this case the Windows OS is not allowed to page out pages that SQL Server allocated for the Buffer Pool. Yes, you read correct: Locked Pages in Memory is only used for Buffer Pool allocations inside SQL Server!
But on the other hand you also have to investigate WHY a Hard Working Set Trim occurred on your system. A Hard Working Set Trim is just the result of another problem that you have on your Windows box. So you should find the root cause, and eliminate it, instead of using the shortcut (enabling Locked Pages in Memory) infinitely. I know a lot of DBAs who are using Locked Pages in Memory by default, and there are several pro and cons for this which I don't want to discuss here again. Glen Berry has a great article which describes more of those pros and cons (see http://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server). One thing that I want to mention here again is the fact that you should limit the memory that SQL Server can allocate for the Buffer Pool when you use Locked Pages in Memory. You can limit the memory through the Max Memory Setting of your SQL Server instance.
By now you know when and how you can enable Locked Pages in Memory for your SQL Server instance. But why the heck Task Manager is reporting a wrong Working Set size when SQL Server is using that privilege. For this explanation we have to dig a little bit deeper into the Win32API. By default (without Locked Pages in Memory) SQL Server allocates memory for the Buffer Pool through the VirtualAlloc function of the Win32API (see http://msdn.microsoft.com/en-us/library/windows/desktop/aa366887(v=vs.85).aspx). But when the SQL Server service account has the Locked Pages in Memory privilege, SQL Server internally uses the AllocateUserPhysicalPages Win32API function (see http://msdn.microsoft.com/en-us/library/windows/desktop/aa366528(v=vs.85).aspx) to do Buffer Pool allocations. This function can be only called by a process, when the process has the SeLockMemoryPrivilege – in other words the Locked Pages in Memory privilege which is the user-friendly name. The Win32API function AllocateUserPhysicalPages is part of the Address Windowing Extensions API (AWE). Therefore those memory allocations can't be paged out by the Windows OS, because AWE memory regions are not page able by design. Those memory regions are just locked in memory, therefore the name of this privilege. When you have Locked Pages in Memory for your SQL Server service account enabled, it also means that you are using AWE indirectly – funny isn't it? As you can see from this explanation, Locked Pages in Memory is rather a Windows OS concept than a SQL Server concept. It has nothing to do directly with SQL Server. SQL Server is just a consumer of it.
With this basic knowledge in your hand, it is now very easy to explain why Task Manager doesn't show the correct Working Set of the SQL Server process: Task Manager is not reporting those memory allocations that are done through the Win32API function AllocateUserPhysicalPages – that's all about this phenomenon.
But how can you now find out, how much memory SQL Server is really using? There are several possibilities. Inside SQL Server you can use the DMV sys.dm_os_process_memory and the column physical_memory_in_use_kb. This returns you the physical memory that SQL Server is currently using including AWE memory allocations. If you want to have a more detailed breakdown of the memory consumption of SQL Server, you can use the DMV sys.dm_os_memory_clerks. A memory clerk is a component inside SQL Server, which tracks memory allocations for a specific component inside SQL Server. SQL Server gives you a memory clerks for each major component for each available NUMA node in your system. The column awe_allocated_kb shows you the AWE memory allocations in kb that were allocated by SQL Server through the Win32API function AllocateUserPhysicalPages.
Update:
Aaron Bertrand (see http://sqlblog.com/blogs/aaron_bertrand) mentioned that it would also make sense to reference a Performance Monitor counter to track the memory consumption of SQL Server. So here it is:
Outside of SQL Server you can use the Performance Monitor counter SQLServer:Memory Manager/Total Server Memory (KB) to track how large the Buffer Pool currently is.
As you have seen with this posting, memory management inside SQL Server is a really complicated topic, and we just have touched the tip of the iceberg. So don't trust Task Manager blindly regarding the memory consumption of SQL Server, you really have to know how SQL Server is configured to get the correct picture.
Thanks for reading
-Klaus
On October 18, 2011 I have made unfair and incorrect words on Twitter, Facebook, LinkedIn, and Xing against the owners of SQLWorkshops.com. Because of this misbehavior of me, I got a so-called dissuasion letter from SQLWorkshops.com, which I have signed today, because I'm committing me now not to make such unfair and incorrect words any more in the future against SQLWorkshops.com.
I also want to take the chance and correct my unfair and incorrect statements of October 18, 2011. It was incorrect and wrong by me that I have blamed SQLWorkshops.com for the following things:
- Unfair Business Conduct ("Some people are not playing fair")
- Sending of threatening emails and letters ("They wrote me and Adam Machanic hard threating emails, because we were presenting on memory spills at sqlpass. At least I and Adam Machanic got a sqlsue email, because we have presented on memory spills at sqlpass.")
- Organizing and running workshops of bad quality and of oversized prices ("They are charging horrible amounts for their workshops. Talked to some customers and they really suck. The attendees are not really amazed. Content is not really that bright…")
- They have made pressure against me to remove my query memory spills demo from my various session downloads.
I apologize for any inconveniences that I have made with these unfair and incorrect statements.
-Klaus
When you sometimes look at Execution Plans, you can see that the SELECT operator has sometimes a so-called Memory Grant assigned. This Memory Grant is specified in kilobytes and is needed for the query execution, when some operators (like Sort/Hash operators) in the Execution Plans need memory for execution – the so called Query Memory.
This query memory must be granted by SQL Server before the query is actually executed. The Query Optimizer uses the underlying Statistics to determine how much Query Memory must be acquired for a given query. The problem is now, when the Statistics are out-of-date, and SQL Server underestimates the processed rows. In this case, SQL Server will also request to less Query Memory for the given query. But when the query actually executes, the query can't resize its granted Query Memory, and can't just request more. The query must operate within the granted Query Memory. In this case, SQL Server has to spill the Sort/Hash-Operation into TempDb, which means that our very fast in-memory operation becomes a very slow physical On-Disk operation. SQL Server Profiler will report those Query Memory Spills through the events Sort Warnings and Hash Warning.
Unfortunately SQL Server 2008 (R2) provides you no events through Extended Events to track down those Memory Spills. In SQL Server 2012 this will change, and you will have additional events available inside Extended Events for troubleshooting this problem. In this posting I will illustrate you with a simple example how you can reproduce a simple Query Memory Spill because of out-of-date statistics. Let's create a new database and a simple test table inside it:
SET
STATISTICS
IO
ON
SET
STATISTICS
TIME
ON
GO
-- Create a new database
CREATE
DATABASE InsufficientMemoryGrants
GO
SUE InsufficientMemoryGrants
GO
-- Create a test table
CREATE
TABLE TestTable
(
Col1 INT
IDENTITY
PRIMARY
KEY,
Col2 INT,
Col3 CHAR(4000)
)
GO
-- Create a Non-Clustered Index on column Col2
CREATE
NONCLUSTERED
INDEX idxTable1_Column2 ON TestTable(Col2)
GO
The table TestTable contains the primary key on the first column, and the second column is indexed through a Non-Clustered Index. The third column is a CHAR(4000) column which isn't indexed. We will use that column afterwards for an ORDER BY, so that the Query Optimizer must generate an explicit Sort Operator inside the Execution Plan. In the next step I'm just inserting 1500 records, where we have an even data distribution across all the values in the second column – each value exists once in our table.
With that test data prepared we can now execute a simple query, which must use a separate Sort operator in the Execution Plan:
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER
BY Col3
GO
This query uses the following Execution Plan:
When you look into SQL Server Profiler and you have enabled the above mentioned events, nothing happens. You can also use the DMV sys.dm_io_virtual_file_stats and the columns num_of_writes and num_of_bytes_written to find out if there was some activity in TempDb for a given query. This works – of course only – when you are the only person who currently uses the given SQL Server instance:
-- Check the activity in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- Select a record through the previous created Non-Clustered Index from the table.
-- SQL Server retrieves the record through a Non-Clustered Index Seek operator.
-- SQL Server estimates for the sort operator 1 record, which also reflects
-- the actual number of rows.
-- SQL Server requests a memory grant of 1024kb - the sorting is done inside
-- the memory.
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER
BY Col3
GO
-- Check the activity in TempDb after the execution of the sort operation.
-- There was no activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
Again you will see no activity in TempDb, which means the output from sys.dm_io_virtual_file_stats is the same before and after executing the query. The query takes on my system around 1ms of execution time.
Now we have a table with 1500 records, means that our table needs 20% + 500 rows of data changes so that SQL Server will update the statistics. If you're doing the math, we need 800 data modifications in that table (500 + 300). So let's just insert 799 additional rows where the value of the second column is 2. We are just changing the data distribution and SQL Server WILL NOT update the statistics, because one additional data change is still missing, until Update Statistics is triggered automatically inside SQL Server!
-- Insert 799 records into table TestTable
SELECT
TOP 799 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT
INTO TestTable
(Col2, Col3)
SELECT 2,
REPLICATE('x', 4000)
FROM #nums
DROP
TABLE #nums
GO
When you now execute the same query again, SQL Server will now spill the Sort operation to TempDb, because SQL Server will only request a Query Memory Grant of 1024 kilobytes, which is estimated for just 1 record – the memory grant has the same size as before:
-- Check the activity in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- SQL Server estimates now 1 record for the sort operation and requests a memory grant of 1.024kb for the query.
-- This is too less, because actually we are sorting 800 rows!
-- SQL Server has to spill the sort operation into TempDb, which now becomes a physical I/O operation!!!
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER
BY Col3
GO
-- Check the activity in TempDb after the execution of the sort operation.
-- There is now activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
If you check the Estimated Number of Rows in the Execution Plan, they are differing completely from the Actual Number of Rows:
When you track the query execution time you will also see that the execution time increased – in my case it increased up to 200ms, which is a huge difference to the earlier execution time of just 1ms! The DMV sys.dm_io_virtual_file_stats will also report some activity inside TempDb, which is also the evidence that SQL Server spilled the Sort operation into TempDb! SQL Server Profiler will also show you a Sort Warning event.
If you now insert one additional record, and you run the query again, everything is fine, because SQL Server will trigger the Statistics Update and estimate the Query Memory Grant correctly:
-- Insert 1 records into table TestTable
SELECT
TOP 1 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT
INTO TestTable
(Col2, Col3)
SELECT 2,
REPLICATE('x', 2000)
FROM #nums
DROP
TABLE #nums
GO
-- Check the activity in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- SQL Server has now accurate statistics and estimates 801 rows for the sort operator.
-- SQL Server requests a memory grant of 6.656kb, which is now enough.
-- SQL Server now spills the sort operation not to TempDb.
-- Logical reads: 577
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER
BY Col3
GO
-- Check the activity in TempDb after the execution of the sort operation.
-- There is now no activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
So this is a very basic example which shows you how you can reproduce Sort Warnings inside SQL Server – not really a magic. Adam Machanic (http://sqlblog.com/blogs/adam_machanic) has done last week at the SQLPASS Summit in Seattle a whole session about Query Memory at a 500 level, where he went into more details on this complicated topic, especially in combination with Parallel Execution Plans.
Thanks for reading!
-Klaus
As I have announced in my "Advanced SQL Server Troubleshooting" session at SQLPASS today, you can find here the slides & samples for download. Thanks for attending my session J
-Klaus
Sie wollen SQL Server Abfragen um 100% oder gar 1000% beschleunigt haben? Gleichzeitig wollen Sie Hardware– und Lizenzkosten Ihrer SQL Server Anwendungen und deren Infrastruktur senken? Ein Wunschdenken? - Keineswegs!
Mit einer gut indizierten SQL Server Datenbank können Sie Geschwindigkeitsvorteile von mehreren 100% für bestimmte Abfragen gewinnen. Als positiven Seiteneffekt senken Sie die Hardwareanforderungen an Ihre SQL Server Infrastruktur und dadurch direkt die Hardware– und Lizenzkosten, die für SQL Server Installation anfallen.
Am 18. Jänner 2012 ist es endlich soweit:
Klaus Aschenbrenner – unabhängigier SQL Server Experte und internationaler Konferenzsprecher – führt den SQL Server Indexing Day in Wien durch.
In 1 intensiven Tag vermittelt Ihnen Klaus Aschenbrenner das notwendige Know-How, um Indizes im SQL Server effektiv einsetzen zu können – wie oben genannt – Performance Optimierungen von mehreren 100% durchführen zu können.
In diesem intensiven Workshop-Tag werden Sie im ersten Schritt den Aufbau von SQL Server Datenbanken und der verschiedenen zur Verfügung stehenden Indizes näher kennenlernen. Aufbauend auf diesem Wissen werden Sie eine Reihe von unterschiedlichen Performance Tuning Techniken näher kennenlernen, die Sie durch den Einsatz des jeweiligen Indexes einsetzen können. Abgerundet wird der Tag mit einem Ausblick auf die Index-Wartung, und über welche Fallstricke Sie hier in Produktionsumgebungen stolpern können.
Inhalte:
Weitere Informationen zum Event können Sie sich unter http://SQLpassion.at/Events.html ansehen, bzw. können Sie den dazugehörigen Folder unter http://SQLPassion.at/IndexingDay.pdf herunterladen.
Veranstaltungsort:
Hotel & Palais Strudlhof
Pasteurgasse 1
A-1090 Wien
http://www.strudlhof.at
Inkludierte Leistungen:
- Workshop von 09:00 - 17:00
- 2 Kaffeepausen (Vormittag, Nachmittag)
- Gemeinsames Mittagessen
Early-Bird Preis bis zum 31.10.2011: € 349,00 exkl Ust.
Danach: € 399,00 exkl Ust.
Anmeldung:
- Anmeldungen werden unter http://SQLpassion.at/Events.html entgegengenommen.
- Ausgestellte Rechungen müssen bis zum Beginn der Veranstaltung einbezahlt werden, damit eine Teilnahme an der Veranstaltung gewährleistet werden kann.
-Klaus
As I have announced in all of my sessions at the SQLbits conferences in Liverpool/UK, you can find here the Slides & Samples for download. Enjoy it and thanks for attending my sessions. J
-Klaus
As I have announced in all of my sessions last week in Budapest at the SolidQ summit, you can find here the Slides & Samples for download. Enjoy it and thanks for attending my sessions J
-Klaus
As I have announced in all of my sessions this week in Rosenheim/Germany at the SQLdays conference can find here the Slides & Samples for download. Enjoy it and thanks for attending my sessions J
-Klaus
Today we covered the topics Full Text Search, Change Data Capture, Change Tracking, and Service Broker. I have taken no notes on Service Broker, because I have well documented that topic a few years ago here
J.
Full Text Search
- Fulltext Index is changed automatically in the background (by default)
- Fulltext Catalog is a container for Fulltext Indexes
- sys.dm_fts_memory_buffers
- sys.dm_fts_memory_pools
- sys.dm_fts_parser
CDC/Change Tracking
- CDC can use the Transactional Replication Log Reader Job
- Sparse Columns are not supported when used in a Column Set
- Computed Columns are not supported
- Individual XML elements are not tracked
- 2 Capture instances are supported per table
- KEEP_CDC option when you restore/attach CDC database backup on a Enterprise Edition
Tomorrow is the last day, where all is about Powershell. But I'm doing tomorrow nothing regarding the training, because I'm trying the SQLMCM Knowledge Exam in the afternoon, and want to have a long sleep with a fresh mind. I'm not expecting to pass the Knowledge Exam on the first try, but at least I know afterwards how hard it is really, and which topics I must study more, and to which level.
With this input I can also recalibrate my studying, because after 3 weeks of SQLskills trainings, it is really hard to find the right level – you can talk with Paul about the actual source code implementation of some components inside SQL Server. But is that really the level to which you have to know it? Tomorrow afternoon I know a little bit more about it…
Thanks for reading
-Klaus
Today Bob spoke about Auditing, SQLCLR, XML, and Spatial Data. Because I already know these concepts very well, my study notes for today are very sparse.
Auditing
SQLCLR
SQLCLR is always loaded, because it is internally used
- Spatial, HierarchyID data types
Only for functions
- WITH RETURNS NULL ON NULL INPUT
- If you can't change the .NET code to use nullable types
SQLCLR uses the Multi Page Allocator
- Uses memory outside the Buffer Pool
- "MemToLeave" area, can be controlled through the "-g" startup parameter on x32
SQLCLR is not controlled by Max Server Memory
- So you need to set Max Server Memory, so that SQLCLR also has some memory available
- You get for each owner of an assembly per database an AppDomain
SQL Server Log shows which AppDomains are loaded
DDL AppDomain
- For Assembly Verification during startup
Runtime AppDomain
- For executing SQLCLR code
- sys.dm_clr_appdomains
- SQL Server calls the .NET GC, when memory pressure occurs
sys.dm_clr_tasks
SQL Server creates invisible assemblies, when you call one assembly from another assembly
- The dependent assembly is invisible
- You can't register .NET code in an invisible assembly
- You have to make that assembly visible by ALTER ASSEMBLY
- Dependent assemblies are automatically dropped when the "root" assembly is dropped
- sys.assembly_references
sys.dm_exec_query_stats
When you execute SQL code in SQLCLR, it's dynamic TSQL Code
- This breaks Ownership Chaining
- Use EXECUTE AS OWNER instead
Thanks for reading
-Klaus
Today Bob spoke the whole day about securing SQL Server, security itself, and encrypting data. It was really hard content, and sometimes it was really hard to follow, because of too much PowerPoint slides. I already miss Paul's whiteboard drawings…  Here are my study notes.
SQL Server Setup
SQL Server VSS Writer
- Needed for 3rd party backup products
- When you change the SQL Server service account password, you don't have to restart SQL Server
When you change the service account through SQL Server Configuration Manager, the Configuration Manager will put the new user in the appropriate group
- Permissions are granted on the group level, never on the user level
- Service Master Key is also re-encrypted through the Configuration Manager
- Registry permissions are also granted on the group level, instead of the account level
NETWORK SERVICE exists only once on a computer
- SQL Server would be shared with other applications, when they are also run under NETWORK SERVICE
- http://sqlskills.com/BLOGS/BOBB/post/About-SQL-Servers-usage-of-Service-SIDs.aspx
- Permissions are assigned to the Service SID of SQL Server
- Service SIDs can't be used for Clustering
- Clustering needs Domain User Accounts
- http://download.microsoft.com/download/1/2/A/12ABE102-4427-4335-B989-5DA579A4D29D/SQL_Server_2008_R2_Security_Best_Practice_Whitepaper.docx
SQL Browser Service
When SQL Server Agent Job is owned by sa
When SQL Server Agent Job is NOT owned by sa
T-SQL steps
- Agent logs into SQL Server
- Uses EXECUTE AS WITH OWNER
Non SQL steps
Kerberos needs a SPN (Service Principal Name) in the Active Directory
- Format: MSSQLSvc/server1:1433
Authentication/Authorization
- Guest account can't be deleted, only deactivated
- sys.tables/principal_id shows the owner of the table
sp_adduser should not be used any more
- Use CREATE USER instead
- sp_adduser creates a schema which is owned by the newly created user
Application Roles
MIsmatched SIDs
TRUSTWORTHY property
- Does the sysadmin trust the DBO?
- fn_my_permissions()
- sys.fn_builtin_permissions()
Windows Groups can't have a default schema
- A user can be in more than one group, which default schema from which group should be associated with the user?
- The last added group (the login with the highest login_id) is used for default language/default database
- SQL Server executes stored procedures as CALLER
- Dynamic T-SQL breaks the Ownership-Chain!
- When the owner of the stored procedure is the same as the owner of the tables that are accessed, permissions are not checked
- The Owership Chain overrides a DENY
Database Master Key is always encrypted with TRIPLE_DES
- The same with the Service Master Key
Service Master Keys are used to encrypt
- Database Master Keys
- Linked Server passwords
- Credentials
Service Master Key is encrypted by
Service Master Key is generated the first time, when SQL Server is started after installation
- Therefore you can clone an installation
- You can regenerate the Service Master Key with the startup option "-K"
Database Master Key can the optionally protected by the Service Master Key
- You don't need a password for opening the Database Master Key
When you backup a database (and therefore the Database Master Key) and restore it on another instance, you will loose the encryption by the Service Master Key
- Therefore you have the option to open the Database Master Key through the provided password which is necessary
- After you have opened the Database Master Key by password, you can add the encryption by the Service Master Key
- When you want to encrypt the Database Master Key by the Service Master Key, you have to supply the password that is used to encrypt the Database Master Key
- TempDb is also encrypted when you enable Transparent Database Encryption for a user database
Thanks for reading
-Klaus
The whole last 3 days I've spent almost every minute with reviewing the material from the last 2 weeks (around 38 hours in total…). Today Bob Beauchemin
joined us, and talks the whole week about SQL Server Development, Security, PowerShell, and some esoteric technologies like SQLCLR, Service Broker, and CDC/Change Tracking.
Don't expect too much notes from this week, because I know a lot of those things already J
Module 1: Optimizing Procedural Code
Implicit Conversation leads to Clustered Index Scan
- Seen in the predicate of the Scan Operator in the Execution Plan
- Unicode <> Non Unique Conversion
- Entity Framework v1 needs Unicode for that reason in the database
Constant Scan
- One Column, One Row RowSet
- Index over a computed column leads to a persisted computed column
Query Compilation
Phase 0
- Initializes the Query Optimizer
- Query cost <= 0.2 will take the plan for execution
- Phase 1
Phase 2
- Hash Join is used when you have no indexes/keys on the table
- When you have a key/index on at least one table, a nested loop join is used
- Each query operator is COM object
- Bitmap IN ROW optimization
- Correlated sub query implies Nested Loop operator
Samples are used when Auto Create and Auto Update Statistics is used
- Depends on the number of pages, around 20% - 30% are used
Service Broker queues have no statistics
- The queues are changing too frequently, so it doesn't make sense to maintain statistics on them
- Density Vector returns the number of distinct rows
- Filtered Indexes/Filtered Statistics are rebuild based on ALL rows, not the filtered rows
sys.dm_os_memory_cache_entries
- original_cost
- current_cost
- DBCC FLUSHPROCINDB(db_id)
DBCC USEROPTIONS shows the SET and all the other session options
- user_id = default schema id
Disable SET ARITHABORT option in SSMS
- Every client that connects to SQL Server has this option also disabled
- If you try to find out, why a query is slow from an application server or other user, you will get within SSMS a new execution plan, because ARITABORT is different
- Therefore the execution plan is compiled, and you get an execution plan for the current supplied parameters
- Therefore you can't reproduce a parameter sniffing problem within SSMS when ARITABORT is enabled!
GROUP BY/HAVING clause
- Query will never get parametrized
- Also not, when FORCED parameterization is enabled on the database
When you need FORCED parameterization on query level
Halloween Protection
- Eager Spool is a Stop-And-Go Operator
- Lazy Spool is a Pass-Through Operator
- E.g. When you're updating a CI in a table
- Table Valued Function
Inline Statement Table Valued Function
- Gets a Parse Tree (cacheobjtype), View (objtype) in sys.dm_exec_cached_plans
- Get no record in sys.dm_exec_query_stats
- You get only a record in sys.dm_exec_query_stats for the statement, that is calling the Inline Statement Table Valued Function
Multiple Statement Table Valued Function
- Gets a Compiled Plan (cacheobjtype), Proc(objtype) in sys.dm_exec_cached_plans
Thanks for reading
-Klaus
As you might know, I'm running my "Advanced SQL Server Performance Troubleshooting Workshop" from September 12 – 14 in Central London. You can find out further information about the detailed agenda here: http://csharp.at/Registration_UK.aspx
Next week you have the amazing possibility to win a free seat in that workshop! It's very easy: Just register as the first person throughout this week (August 21 – August 26) for the workshop and get an additional seat for one of your colleagues for free!
Here are the exact eligibility requirements to get the free seat:
- You have to be the FIRST person who registers for my Performance Troubleshooting Workshop in London, during the week from August 21 – August 26.
- You have to use the discount code TWO4ONE during the registration.
- You have to register the second person also through the discount code TWO4ONE.
- Both registrations must have the same invoice address.
- This offer is only available for persons/companies who haven't yet registered an attendee for the workshop.
- Everyone else who registers throughout the week from August 21 – August 26 with the discount code TWO4ONE, and has not won the free seat, receives a 5% discount of the original price of GBP 1,290.
- The 5% discount does not apply to the first person who wins the additional seat for the workshop.
- The registrations have to be done through the registration site available on http://csharp.at/Registration_UK.aspx
So let's be very fast, and register for my upcoming workshop in London, and if you are the first one, you will get an additional seat for free!
See you soon in London J
-Klaus
Today Jonathan presented on Replication and Paul about Database Snapshots, and his baby called "CHECKDB" – he has been married with CHECKDB for almost 5 years at Microsoft.
Module 8: Replication
- Distributes data to different locations
- Offloading reporting to another database
Transactional Replication
Merge Replication
Snapshot Replication
Distribution database is needed, because there can be several subscribers
SQL Server uses Table Scans inside the Distribution database, which can perform badly when the distribution database gets larger and large
- You can set the retention period
Updatable Subscriptions
- Allows subscribers to replicate changes back to the publisher
Database Mirroring & Replication
- Publisher: Full Support
- Subscriber. Limited Support
Distributor: No Support
- Failover Clustering is the only High Availability option
Failover Clustering & Replication
- Supported on each Replication role (Publisher, Subscriber, Distributor)
Replication Monitor
- Provides health about the Replication topology
Tracer Token
- Allows you to measure latency
Module 9: Database Snapshots
- Consistent point of view of database
- Can be created on a mirror to access database
- Sparse file as a mapping- and data area
- SQL Server is pushing 8kb once a time into the snapshot, not Extents
- Once a page is pushed in the snapshot, it is never pushed again into the snapshot
Buffer Pool has an in-memory map which pages are in the snapshot and which are not
- The first time when the snapshot is accessed the in-memory map is generated
- When the database is grown, the new pages are never in the snapshot, because they never existed in the snapshot when the database was created
- When the original page is pushed into the snapshot, the page is latched (the BUF_LATCH structure)
3 different Snapshots of a database means 3 synchronous writes when a page gets updated
- The original page must be pushed synchronously in each of the 3 snapshots
- Snapshot on a Snapshot will not work
The snapshot stores its own copy of a page in the Buffer Pool, when the page is read
- This can lead to memory pressure in the Buffer Pool
When there are open transactions during the creation of the snapshot
- Crash Recovery is of the source database is run into the snaphot
- E.g. When a transaction is active, a rollback of this transaction is occuring in the context of the snapshot
- When you create a snapshot on a mirror, the consistent point of time of the snapshot is the time, when the last CHECKPOINT operation occured on the mirror
First step is a CHECKPOINT operation, you will not get in the snapshot the following:
- All transactions that have comitted AFTER the CHECKPOINT operation, they are rollbacked
- All transactions that were running at the CHECKPOINT operation, and were not committed at the time the CHECKPOINT operation occured
FILESTREAM file group can't be included in a snapshot, but it does not prevent creating the snapshot
- But you can't REVERT the snapshot, if there is a FILESTREAM file group
Snapshot goes suspect, when there is no more space for the snapshot
- Source database is un-effected, and snapshot can't be used anymore
- You can't detach or restore the source code, when there is a snapshot defined for that database
- http://blogs.msdn.com/b/psssql/archive/2009/03/04/sparse-file-errors-1450-or-665-due-to-file-fragmentation-fixes-and-workarounds.aspx
- sys.databases/source_database_id IS NOT NULL return all Snapshots
- When you begin a transaction and finally rollback the transaction, the changed pages from that transaction stays in the snapshot
- REVERTing a snapshot sets the log file to 0,5MB and 2 VLFs
Database Snapshot Performance Considerations under I/O Intensive Workloads
Module 10: Corruption Detection and Recovery
I/O Errors
823: Hard I/O error
- OS can't read data from disk
824 :Soft I/O error
- Page Checksum failure
Torn Page Detection
- Bits are corrected as soon as the pages are read into the Buffer Pool
- Wrong PageID
825: Read-Retry Error
- Logged in msdb.suspect_pages
Page Protection options doesn't protect you
- They just detect corruption
- Page Checksum is stored in the Page Header
When Page Checksum is enabled, nothing is done
- Automatic Page Repair is available in both Standard and Enterprise Editions
- CHECKDB doesn't take any locks
- Last Known Good (last time when CHECKDB run without any problems) is reported in the SQL Server Error Log, when you start SQL Server
EXTENDEND_LOGICAL_ACCESS
- Indexed Views
- XML Indexes
- Spatial Indexes
- Statistics Blobs are not checked
- Repair can break foreign-key constraints
VLDBs
DBCC CHECKFILEGROUP
- E.g. One partition per each night
- You can run CHECKTABLE on the system tables
- REPAIR_REBUILD option need single user mode
TABLERESULTS
- Returns the output as a table
- Undocumented, because the output can change from release to release
Online Index Rebuild reads the old index during the rebuild
- Offline Index Rebuild also reads almost every time the old index during the rebuild
- You have to drop and recreate the NCI
- It could be problematic if the NCI enforces a constraint, in the mean time when the NCI is dropped, users can insert data that is not enforced by the NCI, and afterwards you can't recreate the NCI
DBCC CHECKDB can be run on a suspect database
- Suspect means that recovery started, but couldn't be completed, so it is not transactional consistent
- But you must put it in EMERGENCY mode
Not all pages can be single page restored (because not all operations on them are fully logged)
- Allocation System belongs to Page-ID 99
- Repair is going to delete data!
- Repair is always offline
When you repair a Replication, you must re-initialize the Subscriptions
- Repair is physically, Replication is logically
- Msg 8921 when TempDb run out of space
- Do not try to repair system tables!!!
- Emergency Mode for rebuilding the transaction log
XVI32 Hex Editor
Damaged PFS page
A suspect or recovery pending database can't be detached on SQL Server 2008, because it can't be re-attached any more...
- When you enforce it, it can be detached
To attach it again
- Create a new database with the SAME log
- Set the database offline
- Delete the MDF/LDF file
- Copy the MDF/LDF file from the detached database
- Set the database online
- Database is again in the SUSPECT state
- ATTACH_REBUILD_LOG
Thanks for reading and stay tuned for the last week J
-Klaus
Today Paul was speaking about Database Mirroring and Jonathan was talking about Failover Clustering – all in all a very intensive day J
Module 6: Database Mirroring
- Split Brain is possible
When you loose the witness, Database Mirroring goes into the asynchronous mode
- No automatic failover possible
- Witness should be also put in the same data center as the principal
- Mirror can't be backed-up, because it's in the Restoring state
You also have to think of your maintenance workload
- Like Index Rebuilds, they have to be send also across the wire
Solution
- Partition your large Index (like 1 TB)
- ALTER INDEX REBUILD on a single partition
- Switch maybe to asynchronous mirroring during the REBUILD phase
Synchronous Mirring
You monitor the
- SEND queue - should be also monitored in synchronous mode (SEND queue > 0 KB)
REDO queue
- Mirror can be only goes online, when the REDO queue is 0
- Can impact the time how long it takes until the Mirror becomes the Principal in a Failover
Pausing/Suspended
- When you have really big transactions like Bulk Loadings
Local Cluster Failover
Failure Detection
- Inside of SQL Server
- Outside of SQL Server
Mirroring a large number of databases
Rolling Upgrades with Database Mirroring
Database Mirroring & Log Shipping
Database Mirroring & Replication
Trace Flag 1462 disables Log Stream Compression
- Turn it on, when you don't get any benefit from Log Stream Compression
- Log Stream Compression is on by default
- Log Compression is also available on Standard Edition of SQL Server
Use Alerts to enable/disable Log Backup Jobs on Old Principal/New Principal
- Automatic Page Repair is available on SQL Server 2008 R2 Standard Edition
Module 7: Failover Clustering
- Preferred owner configuration defines on which node a SQL Server can run
Cluster Validation Tests offline the shared cluster disks
- You have to plan offline time
You need a current Cluster Validation Test Report for Microsoft Product Support Services to get help
- Rerun Validation Tests, as soon as you add a new node to the cluster
Only the active nodes have to be licensed
- The node with the most CPUs defines how many CPUs you have to license when you use a CPU based license
- You need on odd numbe of votes to form a Quorum
Not enable Large Pages, when 2 instances can be run on one node
- The whole buffer pool must be allocated during startup phase of SQL Server
SCSI-3 Persistent Reserveration must be enabled on the LUN level in the SAN
- It's normally not enabled by default
- It's needed for the shared disks in the cluster
TempDb is currently not supported on a local disk in a cluster
- SQL Server Denali will support it
- You can't use currently SSDs als local disk for TempDb
- Network Binding Order must be set with multiple NICs
MSDTC is only needed, when you need distributed transactions
- You can have a separate MSDTC instance per SQL Server instance
Slipstreaming
Failback
- Should be only configured, when the new failover node has less resources as the old one
- Otherwise a failback occurs automatically as soon as the old node gets online
- You need Disk Dependencies if you're using Mount Points
- Virtualization doesn't provide you High Availability during Patching
Rolling Patches/Upgrades
- Go to the SQL Server Instance Properties inside Cluster Manager (for each SQL Server Instance)
- Advanced Policies Tab
- Remove the Possible Owner who you are currently patching
The Instance is taken offline/online during Patching, therefore those steps are needed to be done
- Patching node must be removed from the possible owners
After one instance is patched
- Fail over to the patched instance, after allow it as a possible owner
- Remove possible owner from the unpatched instance
- Patch the unpatched instance
Thanks for reading
-Klaus
Today Jonathan was talking the whole day about Consolidation and Virtualization. There were again a lot of new concepts, and new things to learn.
Module 4: Consolidation
Event Notifications for Monitoring
- Deadlocks, Locking/Blocking Scenarios
- Multiple Instances for TempDb bottleneck
Soft-NUMA should be used to bound multiple instances to dedicated CPUs
- Multiple concurrent Backups can lead to Buffer Pool Contention
Multiple instances when you have high concurrent workloads and not enough worker threads
- Number of worker threads is bound to the instance level
- SQL Server 2008 R2 will report if you run on a Hypervisor
Detach, Copy, Attach
- Maybe you're copying free space over the network
- Raw Device Mapping (RDM)
Migrate Databases through Database Mirroring/Log Shipping
On-the-Fly VHD file migration
Put everything on a VHD
- Including master database etc.
- Move the VHD file from one VM to another VM
There were no providers for 64bit Excel/Access, you can use the following workaround
- Install SQL Express x32
- Linked Server from SQL Server x64 to SQL Express x32
- Linked server from SQL Express x32 to Excel/Access
Multiple Instances because of security issues
- Each database needs sysadmin/securityadmin role
- Put each database on its own instance
Virtual Machine Failover Cluster for Patching SQL Server Instances
- Patch the Passive Node
- Fail over
- Patch the other Node
- Fail back (if needed)
- The total of min server memory should be smaller than the total amount of physical memory available
- Max server memory is ALWAYS preferred for multiple instances
- Memory\Available MBs > 150-300MBs
Thread Pool Starvation
- Not too much worker threads are available
E.g.
SQL Server Consolidation at Microsoft
Module 5: Virtualization
- Too much CPUs can lead to co-scheduling issues
The RAM of a VM should not be larger than a NUMA node
- The assigned number of CPUs should be also not larger than a NUMA node
Min Server Memory setting should be set inside a VM, because of the Balloon driver
- Balloon driver fakes memory pressure in the guests to prevent actual memory pressure on the host
- Ballooning kicks in when you overcommit the host (the assigned memory to the VMs is higher than the physical available memory)
Hyper-V Dynamic Memory not recommended for SQL Server!
When you cluster SQL Server in a Virtual Machine, you have to put each SQL Server node on a different physical Virtual Machine Host node
- Otherwise you have not gained any high availability, when you put each SQL Server node on the same physical Virtual Machine host node
Thanks for reading
-Klaus
Today Paul talked the whole day about HA DR Strategies and Backup/Restore operations. I've already known a lot about Backup/Restores, but Paul showed some cool things that were even new to me J. Here are my study notes for the 3 modules of today:
Module 1: HA DR Strategies
Zero downtime is not possible
- Even not mit Live Migration
Failover Clustering
- Server is protected
- Data is not protected
RPO - Recovery Point Objective
- Maximum allowable data-loss
- Zero data-loss is possible through synchronous Database Mirroring or synchronous SAN replication
What is the transaction log generation rate of your workload?
Impacts
- Database Mirroring
- Log Shipping
- Replication
- Log File Size
Recovery Model
- Database Mirroring needs FULL
- Log shipping works with FULL and BULKLOGGED
Module 2: Backups
- Don't plan a backup strategy, plan a restore strategy!!!
When a Full Database Backup starts, a CHECKPOINT occurs
- CHECKPOINT generates log records
Concurrent Full Backups and Tx Log Backups are possible
- Log Clearing from the Tx Log Backup is deferred until the concurrent Full Backup is completed
- DBCC SQLPERF (LOGSPACE)
- Only a log backup clears the transaction log in the FULL recovery model
- Reverting from a database snapshot breaks the backup chain
While a log backup is running you can't do a minimally logged operation in the bulk logged recover model
- The ML bitmap mask can't be changed in the mean time
Tail Log backup in the bulk logged recovery model also needs the data files (needs to backup the changed extents)
- They must be accessible, not only the transaction log
- Tail Log backup can be performed, but it leads to a corrupt database
- Backup chain is not broken, when you switch between full <> bulk logged <> full
- Backup chain is only broken when you switch to the SIMPLE recovery model
- Database Snapshot can't be created on a database in the RESTORING state because Backup/Restores aren't going through the Buffer Pool
SQL Server can't rebuild a partition, when they are spread across filegroups, where some filegroups are readonly
- Therefore you can use Partitioned Views to union the Read/Write and the ReadOnly filegroups
When you do a Full backup of a readonly filegroup, nothing else (diff, log backup) has to be done
- Just back it up once, and you're done
COPY_ONLY
- Also possible with transaction log backups - will not clear the transaction log
For each backup device a writer thread will be created
- For the restore a seperate reader thread will be created for each backup device
Mirrored Backups
- When a mirror disappears, the entire operation fails
CONTINUE_AFTER_ERROR
- Forces Backups & Restores of corrupt databases
Backup Compression new on SQL Server 2008 Enterprise Edition
- SQL Server 2008 R2 also includes it in Standard Edition
Module 3: Restores
- Files can be only set OFFLINE, but you have to restore from backup to get it ONLINE
Prevent that Backup Compression preallocates space
- Trace Flag 3042
- kb2001026
STOPAT does nothing on a full or diff backup restore
- It's just syntetical reason
After STOPAT a full database backup should be taken to create a new well-known recovery starting point
- Otherwise the restore sequence begins at the initial full backup and all subsequent restores must use STOPAT, which is a way more complicated
fn_dump_dblog
- Look into a backup
- 64 parameters!!!
STOPBEFOREMARK
- Stops the restore process on a specific LSN
Piecemeal Restore
- PRIMARY filegroup must be restored with the PARTIAL option
- E.g. Single Page Restore when a page is corrupt
- Only possible in the FULL or BULK LOGGED recovery model
- Must start with the PRIMARY file group
sp_delete_backuphistory
- Backup history tables in msdb must be cleaned up manually
Startup parameter -Q
- Starts SQL Server without creating TempDb
- Can be used when the model database is damaged, and SQL Server can't create a copy of it for TempDb
- xp_readerrorlog
Option RESTART
- Skips steps during restore, when they are already done
- Writes a .CKP file that stores the different phases
- Stores in the default specified Backup folder
- TF 3004: Instant File Initialization
- TF 3014: What's going during restores
- TF 3605: Print out what caused on TF 3004 and TF 3014
Thanks for reading
-Klaus
My whole weekend was dedicated to MCM studying, and I've reviewed a lot of stuff that we have learned through the last week, and I was also able to read a lot of additional whitepapers and blog posts, that Paul, Kimberly, and Jonathan have referred to us.
Today morning week 3 of the SQLskills training started. This week is all about High Availability and Disaster Recovery and seems to be a lot of fun, and I expect to see a lot of complicated demos and usage scenarios how to combine HADR technologies in SQL Server.
Some already have asked me, how my day looks like, so I want to share my daily schedule here with you:
06:30am
06:30am – 07:00am
- Getting up
- Reading the news that have happened in Austria (I'm 9 hours behind the time in Austria, so it's almost 04:00pm in the evening in Vienna
07:00am – 07:30am
- Video Chat with Karin & Philip
- Philip already enjoys it, and looks every day forward to it, even he is only 18 months old J
07:30am – 08:30am
- Breakfast at SQLskills training, get up and running for the training
08:30am – 05:30pm
05:30pm – 07:00pm
- Some time that is not dedicated to SQL Server
07:00pm – 11:00pm
- Reviewing training material/notes from the day, reading whitepapers, working on some things in SQL Server that I want to try based on the training
This schedule repeats the whole week, expect this week, because the training "only" runs for 4 days.
Thanks for reading
-Klaus
Today I've not taken too much notes, because the whole afternoon was about the Management Data Warehouse and Multi-Instance Management. But before that Paul and Jonathan have spoken a lot about troubleshooting with DMVs.
Module 12: DMVs – Part 2
sys.dm_io_virtual_file_stats
- Spikes can't be seen from this DMV, only through perfmon.exe
I/O Backup activity is tracked by sys.dm_io_virtual_file_stats
- I/Os aren't going through the Buffer Pool, but the I/Os are managed by SQLOS
There can be only a maximum of 32 outstanding I/Os for the log file per database
CHECKPOINT Throttling
- CHECKPOINT process will throttle itself down, when the latency is more than 20ms
- When CHECKPOINT occurs during shutdown, it will throttle itself down, when the latency is more than 100ms
- SQLIO Basics Chapter 2, Page 48
Log Flushes
- Lazywriter uses the BUF structure to know the last 2 times, when a page was accessed
- sys.dm_os_buffer_descriptors can be used to check which database has the highest pressure in the Buffer Pool when memory pressure exists
DBCC CHECKDB disfavours pages read in the buffer pool
- sys.dm_db_index_physical_stats also uses disfavouring
- "bstat" in DBCC PAGE output in page header has one bit, that says if the page is disfavoured or not, but these bits are not documented
- Backups don't touch the buffer pool
- When a scan reads data of more than 10% of the buffer pool, the pages are disfavoured immediately
sys.dm_db_partition_stats
- Gives you page count, instead of using sys.dm_db_index_physical_stats which must touch the index
Version Store is completely no-logged
- TempDb has a lot of different behavior regarding transaction log, therefore the Version Store was put into TempDb and not into the user database
sys.dm_exec_procedure_stats
- Returns runtime statistics about executed stored procedures
- Shared Locks are not acquired for master and TempDb when you set the database context to it
Lock list of the Lock Manager is a dynamic list
- It can change during the reading of sys.dm_tran_locks
- The output of sys.dm_tran_locks is not a precise output
- Lock conversion will not wait indefinitely
sys.dm_os_ring_buffer
- There is a memory clerk for each memory node (Hard- and Soft-NUMA)
- DAC has it's own memory node
sys.dm_os_memory_node_access_state
- Cross NUMA node memory access statistics
- Trace Flag 842 is needed
Implicit Conversions
- Bookmark Lookups can lead to Deadlocks
Thanks for reading
-Klaus
Today Jonathan has presented on analyzing trace data, and Kimberly has spoken on troubleshooting Plan Cache Issues and Index Consolidation. It's really awesome when you listen to Kimberly, because she is speaking so fast, and delivers such a great quality of content – really awesome!!!
Module 10: Analyzing Trace Data
Default Trace
sp_get_query_template to normalize statements can be expensive!
- Cursor must be used, so that you can use it for the result in a trace file
When you consolidate several databases onto a new instance, ad hoc workloads should put together, because they both have problems
- Doesn't make sense to mix these workloads with parametrized workloads
SQLDiag can be used for (Remote) SQL Server Health Checks
- Use Extended Events in it through custom tasks
- SQLskills runs SQLDiag in the snapshot mode for SQL Server Health Checks
- /C 2 compresses the output into a CAB file
- SQLDiag needs sysadmin priviledges on the monitored server
- Blocked Process Report
Module 11: DMVs – Part 1
Thanks for reading
-Klaus
Nothing to say about today, Jonathan did really a great job describing Extended Events and Baselining/Benchmarking to us, and his boss – Paul – did a quick really great overview of Resource Governor. I've done a lot in these areas in the past, so that was a more easy one to me than yesterday J. Here are my study notes for today.
Module 7: Extended Events
- SQL Trace will be replaced by Extended Events some time
- SQL Denali CTP3 has more than 500 Extended Events
- Event Sessions with events with no targets are possible
- Event Sessions with targets and no events are possible
The Event Engine has no idea of Extended Events
- It only provides an operational environment
- Event Sessions have memory buffers attached
- Dispatcher Pool is a worker pool
- Package is a container about events with metadata
- package0 is a container about generic things like targets
- Any event can work with any action with any taraget from any packet
ETW (Event Tracing for Windows)
- Gets information from the Windows Kernel
- ETW & Extended Events are integrated to each other
Fastest first false value should be placed in the first place in a predicate
- Support for short circuit evaluation
- First false evaluation prevents events from firing
You can bind to Global State Data through Actions
Predicates can store state
- You want only to process a event when it is fired the 5th time
- Actions are accessing Global State Data
Actions are executed after a predicate is evaluated
- When an event will not be fired, the action will be not executed
Actions are executed synchronously
By default the Ring Buffer Target uses 4 MB
- ring_buffer target (DMV) can only return 2 MB, therefore mailformed XML can be returned
- Limit therefore the Ring Buffer Target to 2 MB
Denali only provides an asynchronous Bucketizer
- Synchronous should not be used anymore in SQL Server 2008 (R2)
Event Counter Target can be used to check how often an event is fired
- For planning more specific Event Sessions
- With that information you can also redefine your predicates to reduce the events needed to process
Event Pairing Target is used to match events
- Predicates are working on map keys, and not map values
Whitepaper "Using SQL Server 2008 Extended Events" by Jonathan Kehayias
Shred XML data in a table and then do analysis from the table
- Event Sessions are isolated from each other
- It takes 2 microseconds to fire an event, everything else is done asynchronously
Dispatch of async targets occurs under 2 conditions
- The memory buffer becomes full
- Dispatch Latency exceeds
- sys.dm_xe_packages.capabilities is a bitmap mask
Extended Events uses Multi Page Allocations outside of the Buffer Pool
- On x32 this is VAS address space
- This can lead to allocation problems
- Metadata file in depricated in Denali (Async File Target)
- greater_than_max_float64
- less_than_min_float64
- NETWORK_IO = ASYNC_NETWORK_IO in sys.dm_os_wait_stats
MSSQL\Install.u_tables.sql
- Contains the System Health Event Session
Events can't be changed
- They must be dropped from the Event Sessions and added with the changed definition
- Actions are executed syncronously
Causality tracking should be used for event correlation
- Don't add actions for event correlation
- Memory Dumps are placed into the LOG folder
Event Sesions can be automatically started when SQL Server is started
- asynchronous_file_target can be read outside of SQL Server in SQL Server Denai CTP3
- There can be only one ETW session on the entire Windows Server
Troubleshooting Recompiles with Extended Events
- In the first step trace the Recompiles on the server level
- In the second step trace the Recompiles in the database, where the recompiles were highest
- Use the Bucketizer Target for that
- sys.dm_xe_session_targets is the Ring Buffer target
Parameter Sniffing can be troubleshooted through Extended Events
Module 8: Resource Governor
- Enterprise Edition only!
- Can be used to limit MAXDOP on a more granular level
MAXDOP
Resource Governor will override the MAXDOP query hint
- It governs per scheduler
- Resource Governor can't limit existing connections
Connections can't be migrated between workload groups
- Connection must be reset, or disconnect/reconnect
- I/O, TempDb can't be controlled through Resource Governor
- You can limit the number of active connections to a workload group/database
- CPU/IO affinity masks are depricated in Denali
Throtteling is only done when there is contention
- Otherwise CPU limit are ignored
The internal pool can be changed
- The classifier function and login triggers are running in the internal pool
Classifier function must return a SYSNAME
- It can't be a SQLCLR function, it must be a T-SQL function
- But you can call a SQLCLR inside the T-SQL function
- CPU for Backup Compression can be limited through Resource Governor
Perfmon Counters
- SQLServer:Resource Pool Stats
- SQLServer:Workload Group Stats
- You can't tell if a specific query was throttled
- Connections are grouped into workload groups/buckets
- Whitepaper on Resource Governor: http://msdn.microsoft.com/en-us/library/ee151608.aspx
- Parallel Index Rebuilds/Parallel DBCC CHECKDBs are Enterprise Edition only!
You can only have up to 20 resource pools, 18 user defined
- But the max doesn't really make sense
GROUP_MAX_REQUESTS
- Limits the number of concurrent connections (0: unlimited connections)
- Configured on the workload group
Perfmon Counters
- Workload Group Stats: Active Requests
- Workload Group Stats: Queued Requests
- Extended Events have a resource pool id as action
Only for one SQL Server Instance!
- Governing across SQL Server Instances doesn't work
Module 9: Baselining & Benchmarking
Thanks for reading
-Klaus
Today's day was Jonathan Kehayias days, and it was pretty impressive. Jonathan covered the following 3 modules, and he can speak really continuously and forever J
- IO – Storage Area Networks
- IO – Installation and Testing
- SQLOS
I've already read few whitepapers in the past about SANs and also watched the MCM Readiness videos about it, but the material that Jonathan was presenting was pretty impressive. There were a lot of concepts new to me (like Multi Pathing), therefore I didn't have taken any notes in this module, because I have listened to Jonathan and tried to follow him. He also did a very great demo about Multi Pathing with the iSCSCI Target and iSCSI Initiator provided by Microsoft. The SAN module was definitely a module, where I learned around 90% new things.
Important note:
Those notes are the things that I have written down for my personal remembering. So please don't take anything for 100% sure and true in the way that I have written it. For some notes you also have to know the context in which we have discussed it, and why I have taken that note for me personally. Today morning we had a discussion about one specific note that I have taken about CXPACKET waits and why Hyper-Threading can be bad, but at always "it depends", and some things/context information I haven't written down, because I have it somewhere in my brain J.
So here are the notes that I have taken during the 2nd day of SQL MCM training.
Module 5: IO – Installation and Testing
GPT partitions can manage more than 2 TB of data
Spanned disk just provide more size, but not more performance
- Each drive is used sequentially
- Allocation Unit Size should be the size of a single extent
Tools
Module 6: SQLOS
- Each NUMA node gets its own Lazywriter
- MAXDOP: number of physical cores per NUMA node
- http://en.wikipedia.org/wiki/CPU_cache
- Soft NUMA usage: http://msdn.microsoft.com/en-us/library/dd537533(v=sql.100).aspx
- SOS_Worker: Thread
- SOS_Task: Operation that a SOS_Worker executes
- Startup parameter -P24: gets you 24 processors in SQLOS
- Hidden schedulers are processing background tasks
- Visible schedulers are processing user requests
Built-In SQLCLR data types are not supported when Fiber mode is activated
- SQL Server will gives you an error message when you access a SQLCLR data type column
- SQL Server Connections are bound to a scheduler
- Each scheduler has its own waiter list and runnable queue
Scheduler Monitor
- Generates automatically a mini-dump for post-mortem debugging
- Names instances are running on different ports for the DAC
PAE (Physical Address Extension) changes the memory pointer size from 32 to 36 bits
- 64 GBs are addressable in the user mode
- Only usable for the Buffer Pool, NOT for the Plan Cache
/3 GB Option
- Uses 1 GB from the Kernel mode
- That 1 GB stores normally PTEs (Page Table Entries)
- Therefore it's only possible to use a maximum of 16 GB RAM, because of the reduced PTEs
Understanding VAS Reservation
Locked Pages is only for Buffer Pool, not for Plan Cache
- Can be used also by Standard Edition with Trace Flag -T845
- Can be used for Windows System Cache problems
- Startup time of SQL Server takes longer because SQL Server allocates all memory up to the Max Server Memory setting
- The memory must be also contiguous, otherwise SQL Server gets less memory for the Buffer Pool
Memory\Available Mbytes > 150 - 300MB
- Otherwise it means that the Windows OS is under memory pressure
Internal Memory Pressure
- Another Memory Clerk wants to allocate memory
Resource Monitor responds to memory pressure
- Outputs information to the OS ring buffer
- ring_buffer_type = "RING_BUFFER_RESOURCE_MONITOR"
Activity Monitor adds a lot of load to an system which is in performance problems
- NUMA Memory Balancing is not shown through DBCC MEMORYSTATUS
- http://blogs.msdn.com/b/psssql/archive/2009/05/15/how-it-works-dbcc-memorystatus-locked-pages-allocated-and-singlepageallocator-values.aspx
- http://blogs.msdn.com/b/psssql/archive/2010/02/23/how-it-works-sql-server-2008-numa-and-foreign-pages.aspx
- Scheduler can be set offline through the Affinity Mask
Unloading AppDomain on x32
- Indication for Memory Pressure
Interpreting DBCC MEMORYSTATUS
Max Worker Count should be changed to a higher value when you use Database Mirrroing with a large amount of databases
- Mirroring uses at least 3 threads for principal and at least 5 threads for the mirror per database
One worker per level of DOP (e.g. 24)
- 24 Threads for the Producers
- 24 Threads for the Consumers
- 1 Thread for the Coordinator
- For each executed query!
- Those threads are bound to parallel queries and can't be used by anything else!
How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888
-Klaus
Today was my first day of SQL MCM training here in Bellevue. We started at 08:30am and now (05:30pm) Kimberly is currently talking about how to use the provided VPC J. The main theme for today's day was about learning the fundamental about performance tuning. We have covered the following 3 modules:
- SQL Server IO
- Waits & Queues
- IO – Core Concepts
In the morning Paul and Kimberly provided a foundation about how SQL Server is doing IO and the inner architecture of SQL Server. There was a big overlap with the first day of the first week (which I have attended in London), but this was "by design" J. Afterwards Paul jump into Waits & Queues and he has shown a lot of different demos about the various "well-known" wait types like CXPACKET, ASYNC_NETWORK_IO, WRITELOG, etc. Paul also walked down to Latches and Spinlocks, which was really great. Afterwards Jonathan kicked in and told us a lot of IO Core Concepts like the difference between traditional hard disks and SSDs.
I've written down a lot of personal notes for the first day, and the great thing is that I want to share all those notes (and all notes that are coming within the next 3 weeks) with you, so that you can get a picture about the content that we covered. Also a big thanks to Paul, that he allows that! J
Module 1: SQL Server IO
File Header Page in the LDF is a mirror of the Boot Page of the MDF
- SQL Server can write the File Header Page in parallel but NOT the VLFs
- SQL Server has to read all File Header Pages during startup, therefore it is not recommended to have too much databases on a SQL Server instance
- DEFAULT file group should be changed so that new objects go automatically to another file group instead of PRIMARY file group
- When IFI (Instant File Initialization) is enabled you can have a larger Auto Growth setting instead of when IFI is not enabled
When Auto Growth for the Log File kicks in, NO additional log records can be flushed to disk and therefore have to wait
- But only for the database where Auto Growth kicked in, and NOT for the WHOLE instance
FGCB_ADD_REMOVE Latch (File Group Control Block)
- Needed for add/remove files, and Auto Growth
- Can be checked if Auto Growth is a performance issue
The IO subsystem can intersect the partition alignment of Windows Server 2008
- The partition can be therefore ALSO misaligned!!!
File System Level Defragmentation
- Shutdown SQL Server first - corruption can occur!
- Gains maybe 1 - 2% performance (when doing large range scans)
Reads from the TxLog can be sequential or random
Sequential
- Log Backup
- Log Reader Agent for Replication
Random
- Log Records are chained together backwards through their LSN
- Rollback always goes backward, therefore Random IO
- Redo goes forward
- Rollback goes backward
Minimally Logged Operation
- Changed Data Pages are flushed to disk during the COMMIT
- The flush is Extent based
Allocation Weightings
TF 1117
Backups/Restores
TempDb
Perfmon counter "Disk Queue Length" is not relevant
- Outstanding IOs are ok, just look at the latency times from DMV sys.dm_io_virtual_file_stats
Module 2: Waits & Queues
- Waits are just symthoms
- LCK_M_S: Lock Mode Shared
APPEND_ONLY_STORAGE_FIRST_ALLOC
- Latch on the Version Store
- FGCB_PRP_FILL: proportional fill latch
Dead Latches are possible
Dead Spins are not possible
- You can't acquire a Spin, when it is already acquired
- When a Spinlock is acquired, nothing else can't be latched or locked
- SUSPENDED threads are not a ordered list, anyone can be signaled to go into the RUNNABLE state
RUNNABLE is a FIFO queue, expect when Resoure Governor is used
- Workload starvation could be theoretical possible for low priority workload
Propagated Page Split
- Scans from the Virtual Root down to ensure that the Shared Latches are present
CXPACKET
OLEDB
- DBCC CHECKDB uses OLEDB internally
- Microsoft Office (Access, Excel)
WRITELOG
- SSD: 1 - 2ms
- Traditional Hard Disk: < 10ms
Reasons for CXPACKET
Service Broker Wait Types
- Preemptive Wait Types of Win32 IO are indicating that FILESTREAM is used
System table contentation can be removed if you use multiple insertion points (like in TempDb)
- Create a dedicated table for each SPID in TempDb (not a temp table)
Module 3: IO Core Concepts
Thanks for reading
-Klaus
For the next 3 weeks I'm in Bellevue/WA for the SQL MCM training provided by Paul Randal, Kimberly Tripp, Jonathan Kehayias, and Bob Beauchemin of SQLskills.com. So don't expect any technical weblog posting from me within that time. I'm sitting in the following 3 training classes:
- Performance Tuning (5 days)
- High Availability & Disaster Recovery (4 days)
- Security, Powershell & Development Support (5 days)
I have already planned to attend the first week of training in London (Internals & Performance), but I had to cancel it after the first day, because of health issues…L. I arrived yesterday evening from Vienna/Austria, which is a very long trip across half of the world. It took me around 24 hours from door to door until I reached Bellevue. During my 2 long flights (VIE – IAD – SEA) I had plenty of time to go through the SQL MCM pre-reading list and read a lot of different whitepapers. Today's day was also about reading, working, and learning about SQL Server – always with a big jetlag in the back…
Tomorrow is Day 1, and I'm really looking forward to it. I'm trying to blog every day about the training, what I have learned, and how I feel during those 3 weeks of powerful SQL Server training!
Thanks for reading
-Klaus
Since today its official, I'm presenting 2 sessions at the SQLbits conference in Liverpool (http://www.sqlbits.com) from September 30 – October 1. Here are both of my session abstracts:
Advanced SQL Server 2008 Troubleshooting
It's Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this session you will see a lot of different tools with which you can do effective SQL Server troubleshooting. You will learn how to identify performance bottlenecks with those tools, and how to resolve the identified bottlenecks.
Understanding SQL Server Execution Plans
Do you know SQL Server Execution Plans? Yes! - and can you read/analyze them? No... For the beginner it is not very easy to understand and explain execution plans generated by SQL Server for your queries. Therefore this session gives you a good overview and understanding about SQL Server execution plans and how you can read them without reading through complete books. In this session you will see the basic constructs of an execution plan, and how SQL Server uses them to translate your SQL based query to an internal format which is executed by the execution engine of SQL Server. After attending this session you are able to understand and analyze execution plans generated by your queries, and how you can tune them with the adoption of additional indices. Finally you will also see which additional information about your queries an execution plan will expose to you.
I'm looking forward to see you in one or both of my sessions J
-Klaus
A few days ago I had an interesting conversation on Twitter about my upcoming speaking engagements. I really realized at this time that I have a lot of different SQL Server conferences in my pipeline where I'm speaking over the next months. Here are the conferences where you can meet me, and talk about SQL Server related topics.
- September 12 - 14, London/United Kingdom:
I'm doing my own "Advanced SQL Server Performance Troubleshooting Workshop", which is aimed for the senior DBA/DEV with experienced know-how in SQL Server. You can find further information about the workshop at http://www.csharp.at/events.html where you can also place your registration. If you are registering by the end of this month, you will get a discount of GBP 200!
- September 23, Solid Quality Summit, Budapest/Hungary, http://www.sqlu.com
I'm doing 3 sessions about SQL Server performance troubleshooting (what else?). I'm talking about Database Internals (yes, that's THE session from SQLbits 8 J), Troubleshooting TempDb Performance, and Advanced SQL Server Performance Troubleshooting Techniques
- September 26 – 28, Vienna/Austria
I'm doing again my own "Advanced SQL Server Performance Troubleshooting Workshop" in Vienna, which is again aimed for the senior DBA/DEV. I'm also able to give you a EUR 100 discount, if you register by the end of this month at http://www.csharp.at/events.html
- September 29, SQLdays, Rosenheim/Germany, http://www.sqldays.net
SQLdays is a nice and niche SQL Server conference in South Germany (in Bavaria) where they have really good beer J. I'm again doing 3 performance troubleshooting sessions about "Maintaining VLDBs", "Troubleshooting TempDb", and "Advanced SQL Server 2008 Troubleshooting Techniques".
- September 30 – October 1, SQLbits 9, Liverpool/United Kingdom, http://www.sqlbits.com
I've submitted several advanced sessions about troubleshooting SQL Server related performance problems. I don't know if I can speak, but I'm speaking regularly the last years, so if I get accepted, I will be of course there!
- October 10 – 14, SQLPASS, Seattle/USA, http://www.sqlpass.com
That's the really great one! I'm speaking at the SQLPASS Community Summit regularly since 2006 (expect 2008 because of personal reasons), so this is now my 5th contribution to the best SQL Server conference on earth! I was one of the first European speakers that got a spotlight session in 2007! I'm again doing my famous session about "Advanced SQL Server Troubleshooting Techniques".
- November 7 – 9, Zurich/Switzerland
I'm doing my own "SQL Server Performance Tuning & Troubleshooting Workshop" in Zurich/Switzerland, which is aimed for professional DBAs/DEVs. This workshop is very new, because I've announced it yesterday evening J. You can find further information about the workshop at http://www.csharp.at/events.html
- November 10, TBD
On November 10 I'm speaking on another SQL Server conference in Europe, but at this time I'm not allowed to tell you any more things about it… ;-(
As you can see from this list, there a lot of opportunities, where you can meet me face-2-face and talk to me about SQL Server related topics. As you can also see I'm doing a lot in the area of performance tuning and troubleshooting. If you need help in these areas of SQL Server, don't hesitate to contact me.
The cool thing is that I'm doing my session about "Advanced SQL Server Troubleshooting Techniques" several times within the next months, so you can expect here a really awesome content. And trust me, this session will differ from everything you have seen in the past, I'm trying to deliver that session in a really new way with some fun for you!
See you soon & thanks for reading!
-Klaus
Yesterday we got confirmation from SQLPASS if our submitted sessions for the upcoming SQLPASSS summit in Seattle (October 11 – 14) were accepted. I’m very lucky to announce that my submitted session “Advanced SQL Server 2008 Troubleshooting” made it to the summit as a regular session (75min)!  Here’s the abstract that I have submitted: It's Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this session you will see a lot of different tools with which you can do effective SQL Server troubleshooting. You will learn how to identify performance bottlenecks with those tools, and how to resolve the identified bottlenecks. I’m really proud of presenting this session, because all my work from the past and from the future is about SQL Server performance tuning and troubleshooting. I’ve also presented a 1-day workshop at the previous SQLbits conference about this topic, where I got really good and promising feedback. See here for some attendee voices. I’m also organizing several 3-day long workshops across Europe about this topic. So, if you are not able to attend the SQLPASS summit, but you are interested in that topic, you can also join one of my scheduled workshops here in Europe – see http://www.csharp.at/events.html for further information. See you soon in Seattle! -Klaus
The most amazing journey that I have done in the past years was definitely SQLcruise (see http://www.sqlcruise.com) – a SQL Server event organized and running by Brent Ozar (Blog | Twitter). Without the speakers, and the sponsors we were 14 cruisers that were flying to Seattle across the world for attending SQLcruise. Our journey took us from Seattle to Juneau, to Skagway, to Glacier Bay, to Ketchikan, to Victoria, and finally back to Seattle. 
Our home for the 7 days long cruise was the Norwegian Pear, one of the biggest cruise ships I have ever seen in my life:  
We left Seattle at Sunday (May 29) at 04:00pm. Here are some impressions cruising out of Seattle and enjoying its skyline.  
During the first days on sea we had a session from Buck Woody (Blog | Twitter) about career, where we have seen that Buck is a really good story teller. Tim Ford (Blog | Twitter) had a session about DMVs/DMFs in SQL Server and how you can organize them through a periodic table – very interesting concept  . Brent also gave a session about SANs and how you can test and improve their performance. After cruising the whole Monday and the half of Tuesday we arrived around 02:00pm on Tuesday in Juneau, where I did an excursion to the Mendenhall glacier. Here are some impressions:  
After leaving Juneau Kendra Little (Blog | Twitter) did a really great presentation on Isolation Levels and Locking inside SQL Server. As you might know from her weblog NOLOCK is also acquiring some locks  . Wednesday morning we arrived at Skagway were I took a train and went up to the White Pass Summit to the border of Canada. It was really awesome when you finally see all the snow and you know you started at an elevation of 0…  
 
After leaving Skagway we went to Glacier Bay, the most interesting part of the whole cruise. Glacier Bay consists of a lot of different fjords, where you can see a lot of different glaciers and a lot of wild life.  
 
 
After we left Glacier Bay, Buck did a presentation on Communication, where we learned a lot about how to communicate and which different variations of communication exists, even when you are writing weblog posts like this. On Friday morning we arrived in Ketchikan, where I did a kart tour adventure. Also very interesting and I had a lot of fun!  
With those karts we drove around 15 miles on very uneven forest streets “somewhere” around Ketchikan. Those karts were able to speed up to 30 mph! After leaving Ketchikan Brent did a presentation on Defensive Indexing which was also very good and impressive. Brent also talked a lot of benchmarking and about his famous Excel sheet, where he collects PerfMon counters. On Saturday evening we arrived in Victoria, where I decided to stay on board and have the last night some fun at the various bars on the ship with some other cruisers.  
On Sunday morning we arrived back in Seattle, where I left the Norwegian Pearl around 08:30am and stayed the whole day in Seattle. My flight back to Vienna was leaving on Monday morning at 07:30am ;-(    
I have also to mention the sessions that Grant Fritchey (Blog | Twitter) from Redgate, and Aaron Bertrand (Blog | Twitter) from SQL Sentry gave! Without those sponsors SQLcruise would not be possible!!! Thanks -Klaus
As announced in both of my SQL Server sessions at DevConnections in Karlsruhe/Germany you can find here the slides & samples for download: As also said yesterday, you get a 10% discount for my upcoming “Advanced SQL Server Performance Troubleshooting Workshop” - you can find here further information about the workshop and the registration details: http://csharp.at/Events.html
Thanks for attending my sessions  -Klaus
I’m working my whole professional work life (around 11 years) with SQL Server, and have see so many pitfalls in configuration options, deployments, and database designs. From all this know-how I’ve done in the past a lot of consulting engagements and have spoken at the various SQL Server conferences across the world about performance tuning and optimization. In sum there are so many things that you just HAVE to know about SQL Server, so that you can use SQL Server with the best possible performance. Today I’m very proud to announce that I’m now offering an advanced SQL Server Performance Optimization and Troubleshooting workshop at the international level! We will be starting with our first public course in Central London/UK from September 12 – 14, and offering a second public course in Vienna/Austria from September 26 – 28. Further information about the detailed agenda and the registration details can be found at http://www.csharp.at/events.html. We have also several other locations across Europe in our pipeline, like: - Zurich/Switzerland
- Oslo/Norway
- Germany
If you want to see some feedback about what you can expect in this workshop, please refer to the following weblog post from me, where I have given a deep technical session about SQL Server Internals at the SQLbits conference this year in Brighton/UK: http://www.csharp.at/blog/PermaLink,guid,f1d784c8-ea53-4bc9-99df-6dfaab6f6f8c.aspx I’ll be looking forward to see some of you at one of these advanced SQL Server workshops! -Klaus
It’s already a long time ago, since I spoke at SQLbits (first week of April), but yesterday the speakers got their session evaluation, and I’m very proud about the results of the 2 sessions that I have done at SQLbits: - Service Broker: Message in a Bottle
- SQL Server 2008 Database Internals
The following 2 figures shows you the overall session metrics for both of my sessions: 
As you can see my “SQL Server 2008 Database Internals” was very high rated and nearly a top rated session  . I want to share you the feedback that I got for this session: - Best talk of the day, absolutely fantastic knowledge of the internals of SQL Server.
- A very good session the presenter was also good, I learn't a lot and the speaker used a methodical and logical approach to explaining pages/extents and I/O.
- Although I scored this higher than the previous session,this is on reflection after the event - I've had a few 'A-ha' moments since attending, and the content itself was brilliant - Klaus really knows his stuff in this area. It was a VERY large room to present in, the audio wasn't quite as good as some others, and the display screen was quite low so it couldn't be seen by people more than 5 rows back.
- Amazing depth - thanks, Klaus! Presentation skills generally good – real passion for the subject.
- Klaus is obviously very knowledgeable and a very good presenter. I think because of the deep technicality of the subject, it's easy to lose focus, especially as it was just before lunch. Although I got lots out of the session there were a couple of occasions where I drifted off a bit. This isn't a critism of Klaus - it was just a very deep session that he was cramming into one hour.
- Hugely enjoyable and very professional presenter. Conveyed enthusiasm and knowledge in equal measure. Excellent!
- Really great in depth knowledge.
- Brilliant speaker. Speaker has a great deal of knowledge in the subject and was able to convey it effortlessly, with a little humor
 Fantastic session that made the 8 hour journey well worth it. - Very entertaining presentation on a somewhat dry subject.
- Very knowledgeable and engaging.
- Very good session.
- Great enthusiasm and knowledge.
- I was working on FoxPro since last 18 years and I am in view that few features in SQL server 2005 onwards had taken from FoxPro. I am quite aware of FoxPro database/table structure and exactly what looking for SQL in last few years. It does make impact on how you design you database, when you know how it is stored.
- I also attended Klaus' sessions on the Thursday; I was very very impressed with his abilities as a speaker and the way in which he went through each issue. Everything was exceptionally clear, well prepared in terms of everything being prescripted in the demos and very clear on the results and advantages to understand each aspect. I would say he was the best speaker of any session I attended during the three days.
- That was very cool, Klaus. Fascinating. Perhaps not something I *need to know, but I certainly came away with a much deeper and better understanding, feeling a lot more on top of the beast that is SQL server!
Here’s the feedback for my Service Broker session that I also want to share with you: - Really useful presentation especially as we are attempting to use service broker. He solved several problems I was experiencing with Service broker, including the alternative windows authentication over certification method. I would have liked to know more about the transport level issues but it was a 1 hour session and any more detail would cause the my brain to fry. it has left me wanting to go on the full day session if it is done again at SQL BITs.
- Excellent presentation, as a newbie i'd not used Service Broker yet but Klaus made this an enjoyable and easy to learn experience.
- Session was very good but I was expecting about how service broker can be used in HA/DR scenarios.
- I attended this session to see Service Broker working as an SOA platform. Klaus demonstrated this very well.
- I would have liked the session framed better in relation to other technologies and decisions to make.
- The one suggestion I would make is to include a few situations where service broker would be useful vs where it might not be useful and also to compare more with other architectures that are similar (e.g. MSMQ).
- Content aside (which in itself was still outstanding), this is the best delivery of a presentation I've seen in a long time.
- you could see Klaus has a passion for this :)
- Brilliant session
- Klaus's enthusiasm for his subject is obvious. A really good, demo-heavy session on a subject I am not very familiar with which was engaging and fun. Very enjoyable!
The main problem that I had with my Service Broker session is TIME  . One hour is just too less to explain a fully distributed, and secure Service Broker application. Presenting a 75min or even 90min session on that topic would make it really awesome . My (positive) learning from SQLbits is that the UK boys and girls like my presentation style and my topics, so I’m planning to do much more within the next months in the UK. Stay tuned for further information on that! -Klaus
As announced in both of my sessions, you can download the slides & samples for my SQLbits sessions here: Thanks for attending both of my sessions – it was a real fun for me to present them to you  -Klaus
Sind Sie schon mal in das folgende Problem-Szenario gelaufen, und mussten Ihren wohlverdienten Feierabend und die Vorfreude auf das Wochenende opfern: Freitags - 16:30. Sie erhalten eine Email, dass Ihre SQL Server Installation in enorme Performance-Probleme geraten ist! Was nun? Wie können Sie das Problem schnell identifizieren und effektiv lösen? Welche Tools stehen Ihnen dazu mit dem SQL Server 2008 (R2) zur Verfügung? Am 24. Mai ist es endlich soweit: Klaus Aschenbrenner – unabhängigier SQL Server Experte und internationaler Konferenzsprecher – führt den SQL Server Performance Tuning und Troubleshooting Workshop in Wien durch. In einem kompakten und intensiven Tag zeigt Ihnen Klaus Aschenbrenner die richtigen Tools und vermittelt Ihnen das notwendige Know-How, um in Problem-Szenarien – wie oben genannt – schneller und effektiver zu einer sauberen Lösung zu kommen. In diesem Workshop werden Sie eine Reihe von unterschiedlichen Tools kennenlernen, mit denen Sie ein effektives Troubleshooting einer SQL Server 2008 (R2) Installation durchführen können. Sie werden einerseits lernen, die entsprechenden Problembereiche anhand bestimmter Scripte/Tools zu identifizieren, um diese anschließend aufgrund der gewonnenen Erkenntnisse zu beseitigen. Inhalte: · SQL Server Performance Monitoring Methologie · Troubleshooting Execution Plans & Index Usage · Troubleshooting Memory/CPU Probleme · Troubleshooting TempDb & IO Subsysteme · Performance Monitoring mit SQLDiag/SQL Nexus Weitere Informationen zum Event können Sie sich unter http://www.csharp.at/Events.html ansehen, bzw. können Sie den dazugehörigen Folder unter http://www.csharp.at/Downloads/SQLFlyer.pdf herunterladen. Veranstaltungsort: Hotel & Palais Strudlhof Pasteurgasse 1 A-1090 Wien http://www.strudlhof.at Inkludierte Leistungen: · Workshop von 09:00 - 17:00 · 2 Kaffeepausen (Vormittag, Nachmittag) · Gemeinsames Mittagessen · Early-Bird Preis bis zum 08.04.2011: € 349,00 exkl. · Danach: € 399,00 exkl. Anmeldung: · Durch Email an klaus.aschenbrenner@csharp.at mit Angabe der Rechnungsadresse. · Ausgestellte Rechungen müssen bis zum Beginn der Veranstaltung einbezahlt werden, damit eine Teilnahme an der Veranstaltung gewährleistet werden kann. · Bei weniger als 10 Teilnehmern behält es sich der Veranstalter vor, die Veranstaltung abzusagen, wodurch bereits einbezahlte Rechnungen zurückerstattet werden. -Klaus
As I have announced in both of my sessions at the VSone conference in Munich, you can find the session material here for download: Thanks for attending both of my sessions  -Klaus
As announced in my DWH workshop in Munich, you can find here my whole ETL solution. Thanks for attending my workshop  -Klaus
As announced in both of my session you can find my slides & samples here for download: Thanks for attending both of my sessions  -Klaus
On January 4, I’m speaking about index internals for the Performance Virtual Chapter of PASS (http://performance.sqlpass.org). Here’s my session abstract: SQL Server provides two kinds of indexes for relational data: Clustered- and Non-Clustered Indexes. Both of them can be defined as unique and non-unique. In this session you will learn how to use Clustered- and Non-Clustered Indexes in your database and how you can improve the execution time of your queries with those indexes. Furthermore we will have a look into the concrete storage details of both indexes and how they differ when you define them as unique and non-unique and what's the tradeoff between it. Finally you will get some best practices how to deal effectively with Clustered- and Non-Clustered Indexes in your database. I’m really looking forward to this great chance, and hope that you attend my session. -Klaus
I’m one of the 3 lucky guys whose submitted precon workshop for the developer category for PASS SQLRally is in the final selection for community voting! Here’s the abstract that I have submitted which gives you a good overview what I’m talking about, and what you’ll learn in my precon. If you think this sounds good for you, and you want to hear me at SQLrally, please vote for my precon workshop. Voting should be available within the next days at SQLRally.com. What every .NET developer MUST know about SQL Server
Are you building database applications with the .NET framework for SQL Server? You know how you can perform INSERT, UPDATE, DELETE, and SELECT statements, but all the other areas of SQL Server are a black box for you? And sometimes you don’t know why your database application is getting slower and slower and consume more and more memory on SQL Server? If you are answering one of these questions with yes, then you are the right person for this intensive 1-day long SQL Server internals workshop presented by Klaus Aschenbrenner. In this workshop you will learn the inner workings of SQL Server, and how you can use this know-how when building your next database driven .NET application for SQL Server.
Klaus will give at the beginning an general overview about SQL Server, its architecture, and how SQL Server uses Execution Plans to execute a query. You will also learn about Plan Cache Polution, why it is bad, and how you can avoid it, when working with the .NET framework. After laying out these foundations, Klaus moves into the storage details of databases and tables, and shows you how SQL Server physically stores tables in the database file and how you can fine tune the physical storage with correct chosen data types and an intelligent table design. After you know the basics of Execution Plans and storage details, Klaus teaches you the architecture and the inner workings of indexes in SQL Server. You will see the differences between Clustered- and Non-Clustered Indexes and how they relate to each other. With this knowledge in your hand, you will then finally see how you can use different index tuning methods to speed up the execution time of your database queries.
Agenda - Overview about query execution in SQL Server 2008
- Database Internals
- Database Structure
- Page-Design
- Fixed data types
- VAR data types
- LOB data types
- Sparse Columns
- FILESTREAM Storage
- Data Modification Internals
- Transaction Log
- Architecture
- Backup/Recovery
- Recovery Strategies
- Execution Plans
- Reading & Understanding
- Operators
- Physical JOIN operators
- Capturing Execution Plans
- Procedure Cache/Procedure Cache Polution
- SQL Server Index Architecture
- Heap Tables
- Clustered Index
- Non-Clustered Index
- Index Depth
- Clustered Key/Clustered Key Dependencies
- Unique/Non-Unique Clustered Key (UNIQUIFIER)
- Clustered Index Structure
- Unique Clustered Index
- Non-Unique Clustered Index
- Non-Clustered Index Structure
- Unique Non-Clustered Index
- Non-Unique Non-Clustered Index
- Non-Clustered Index on Heap Tables
- Non-Clustered Index on Clustered Tables
- Index Tuning
- Covering Index
- Avoiding Bookmark Lookups
- Index Intersection
- Index Join
- Index Access Methods
- Filtered Index
- Indexed Views
- Statistics
- Usage in SQL Server 2008
- Auto Stats
- Statistics Maintenance
- Analyzing Statistics
- Multi-Column Index Statistics
- Fill Factor
- Index Reorganize
- Index Rebuild
- Online Index Rebuild
- Statistics & Table Variables
-Klaus
As announced in my workshop and in my sessions at the SQLdays 2010 last week in Rosenheim/Germany, you can download my shown slides & samples from here: Have fun and thanks for attending my sessions/workshop -Klaus
As announced in my SQLBits workshop & session last week in York/UK, you can find the session/workshop material here. Have fun! -Klaus
As announced in my StreamInsight session at 24HOP you can download the session material from here. Within the next weeks I will also provide a detailed weblog posting which describes the techniques I used in this sample. Thanks -Klaus
19.-20. Oktober 2010, Rosenheim SQLdays ist die Konferenz für die deutschsprachige SQL Server Community vom 19.-20. Oktober 2010 in Rosenheim. Freuen Sie sich auf drei parallele Tracks mit über 20 Sessions zu SQL Server Administration, Entwicklung und Business Intelligence. Treffen Sie Datenbank- und BI-Experten wie Klaus Aschenbrenner, Markus Raatz, Steffen Krause, Thomas Grohser oder Willfried Färber persönlich und erfahren Sie News aus erster Hand und wertvolle Tipps und Tricks aus der Praxis. In ganztägigen Workshops vor und nach der Konferenz haben Sie zudem die Gelegenheit spezielle Themen nochmal bis ins Detail zu vertiefen. Sichern Sie sich jetzt Ihre Teilnahme unter www.SQLdays.net
As announced in my sessions and my workshop at the Solid Quality Summit in Vienna, you can download the materials from here: Thanks for attending my sessions and have fun -Klaus
As announced in my Developer Camp workshop in Berlin you can download my workshop material from here: Thanks for attending my workshop and have fun -Klaus
As announced in my European PASS session you can download my session material from here: Thanks for attending my session and have fun -Klaus
As announced in my VSOne sessions and in my workshop you can download my session material from here: Thanks for attending my sessions and have fun -Klaus
As announced in my FILESTREAM talk you can download my session material from here:
Have fun and thanks for attending my session
-Klaus
As announced in my SQLdays sessions and in my workshop you can download my session materials from here: Have fun -Klaus
SQL Server 2008 Developers Summit - Das Power-Event zur SQL Server 2008 Programmierung!
Lernen Sie konzentriert das, was Sie zur SQL Server 2008 Programmierung benötigen.
Sichern Sie sich diesen Herbst Ihren Technologievorsprung zum SQL Server 2008!
Zielpublikum: SQL Server 2005 Programmierer/innen
Termine: Montag 14.12.2009 bis Freitag 18.12.2009
Ort: Palais Strudlhof, Pasteurgasse 1, A-1090 Wien, Anfahrtsinformationen
Kosten: € 1.300,00 (im Betrag ist keine Umsatzsteuer enthalten)
Es ist ein eigenes Notebook mitzubringen!
Tagesablauf:
- 09:00 - 13:00 Workshops
- 13:00 - 14.00 Mittagessen
- 14:00 - 17:00 Workshops
- Abends: Abendprogramm zum Ausklang des Tages
Das ergibt 5 x 8 Stunden gepowertes SQL Server 2008 Know-How zum Preis von nur € 1.300,00. Dieser Preis beinhaltet:
- 5 Tage Power-Workshops zum .NET SQL Server 2008
- Aktuelles Buch zum SQL Server 2008
- Begrüßungskaffee/Tee
- Vormittagsjause
- 3-gängiges Mittagessen
- Nachmittagsjause
- Konferenzgetränke
Erfahren Sie alles, was Sie zur SQL Server 2008 Entwicklung in der tagtäglichen Arbeit benötigen! Top-Themen:
- Resource Governor
- Policy based Management
- Data Collector
- Change Data Capture
- T-SQL 2008
- Business Intelligence Inside/Outside
- Managed SQL Server 2008 Programmierung
- Preview: SQL Server 2008 R2!
Weitere Informationen zum genauen Inhalt und zur Anmeldung finden Sie unter http://developers-summit.csharp.at
-Klaus
After a long time of work, I finally finished today my presentations for the Solid Quality Summit which takes place next week here in Vienna. I’m presenting the following sessions:
- ADO.NET Enhancements
- Spatial Data support in SQL Server 2008
- Service Broker Scaleout scenarios
- T-SQL Enhancements in SQL Server 2008
On friday I’m doing a whole day workshop about the SQLCLR integration in SQL Server 2008. As you can see, I have a lot of work to do next week. I’m looking forward to next week, to see many good friends from Solid Quality
-Klaus
As announced in both of my sessions at the European PASS conference in Neuss/Germany last week, you can download the sample code here:
I hope you have enjoyed my sessions
-Klaus
Wir nähern uns mit großen Schritten dem Solid Quality Summit vom 22. bis 26. Juni, dem SQL Server Event des Jahres in Wien. Nun ist auch der Session Schedule fixiert. Einige Mentoren wie Dejan Sarka, Davide Mauri oder Klaus Aschenbrenner schleifen auf der European PASS Conference in Neuss gerade Ihre Sessions zurecht, um für das anspruchsvolle Publikum in Wien optimal vorbereitet zu sein. Aber auch Itzik Ben-Gan, dessen Buch „SQL Server 2008 Query Tuning“ soeben erschienen ist, Herbert Albert, Gianluca Hotz und die anderen Vortragenden scharen bereits in den Startlöchern, um Sie an ihren neuesten Erkenntnissen teilhaben zu lassen. Erleben Sie die einzigartige Kombination aus High-End Content und familiärer Atmosphäre beim Solid Quality Summit in Wien!
Alle Detailinformationen zum Summit finden Sie hier.
Anmeldeseite:.
Seminare:
Montag, 22.06.2009:
Itzik Ben-Gan: Inside T-SQL for SQL Server 2005 and 2008 (Room1)
Herbert Albert: Disaster Recovery (Room2)
Freitag, 30.06.2009:
Dejan Sarka: Data Modelling Essentials (Room1)
Klaus Aschenbrenner: Managed Code Development with SQL Server 2008 (Room2)
Session Schedule:
|
Day |
Time |
Speaker |
Room1 |
Speaker |
Room2 |
|
23.06.2009 |
09:00-10:15 |
Itzik Ben-Gan |
Minimally Logged Inserts and other Data Modification Enhancements in SQL Server 2008 |
Dejan Sarka |
Advances in Relational Databases |
|
10:30-11:45 |
Aaron Johal |
Help! My operational system is getting slower and slower! |
Helmut Knappe |
Implementing Many-to-many Relationships in SQL Server Analysis Services to Model Complex Business Solutions |
|
13:30-14:45 |
Itzik Ben-Gan |
Islands and Gaps Problems |
Michael Sass |
Meta Data Locks - Analysis and Performance Problems |
|
15:00-16:15 |
Aaron Johal |
Using Visual Studio Team System 2008 Database Edition to resolve the data perception |
Helmut Knappe |
Charts in Reporting Services 2008 |
|
16:30-17:45 |
Itzik Ben-Gan |
Advanced T-SQL Tips & Tricks |
Andreas Schindler |
Best Practice ETL |
|
|
|
|
|
|
|
24.06.2009 |
09:00-10:15 |
Klaus Aschenbrenner |
Scaleout scenarios with SQL Server & Service Broker |
Dejan Sarka |
Understanding XQuery, |
|
10:30-11:45 |
Itzik Ben-Gan |
Using the HIERARCHYID Datatype in SQL Server 2008 |
Davide Mauri |
Set Based solution: an approach for developers |
|
13:30-14:45 |
Klaus Aschenbrenner |
T-SQL Erweiterungen im SQL Server 2008 |
Gianluca Hotz & Herbert Albert |
Implementing SQL Server 2008 Policy Based Management |
|
15:00-16:15 |
Itzik Ben-Gan |
Aggregating Data in SQL Server 2008 Using Grouping Sets |
Gianluca Hotz & Herbert Albert |
SQL Server 2008 Policy Based Management and multi-server administration |
|
16:30-17:45 |
Klaus Aschenbrenner |
ADO.NET Enhancements für SQL Server 2005/2008 |
Davide Mauri |
Instrumenting, Monitoring and Auditing of SSIS ETL Solutions |
|
|
|
|
|
|
|
25.06.2009 |
09:00-10:15 |
Dejan Sarka |
Design Myths |
Andreas Schindler |
Solving Business Problems with MDX |
|
10:30-11:45 |
Helmut Knappe |
Business Intelligence Myths |
Aaron Johal |
Why must I shoehorn my data into tables? I need total flexibility to develop in an agile way! |
|
13:30-14:45 |
Dejan Sarka |
Temporal Data in SQL Server 2008 |
Andreas Schindler |
Change Management für Analysis Services Cubes |
|
15:00-16:15 |
Gianluca Hotz |
Oracle to SQL Server migration for Developers I |
Helmut Knappe |
Using Windows PowerShell in BI |
|
16:30-17:45 |
Gianluca Hotz |
Oracle to SQL Server migration for Developers II |
Klaus Aschenbrenner |
SQL Server 2008 und der GEOGRAPHY Datentyp |
Preise:
Full Summit 5 Tage inkl. 1Day Classes(regulär): 1.990,00 Euro (exkl. Ust.)
Full Summit 5 Tage inkl. 1Day Classes(Early Bird): 1.790,00 Euro (exkl. Ust.)
Early Bird bis 11.05.2009
1Day Class only 590,00 Euro (exkl. Ust.)
Slim Conference 3 Tage 1.490,00 Euro (exkl. Ust.)
Anmeldung:
Für Gruppen ab 3 Teilnehmern gibt es spezielle Ermäßigungen. Näheres dazu erfahren Sie per Mail unter flechnitz@solidq.com bzw. am Telefon unter +43 (676) 5397927
Bitte beachten Sie bei der Anmeldung für 1Day Classes, dass Full Conference Teilnehmer bevorzugt behandelt werden, sollten die vorhandenen Plätze knapp werden.
Ort:
it-versity
Schottenfeldgasse 69
1070 Wien
Wir freuen uns darauf, Sie im Juni beim Solid Quality Summit 2009 in Wien, dem SQL Server-Event des Jahres begrüßen zu dürfen.
As announced in my session at the SQLBits conference in Manchester, you can download the sample code here. I hope you enjoyed my session and my attractive back
-Klaus
As announced in both of my sessions here at the DevWeek conference in London, you can download the sample code here. But the download files will be online on Sunday, when I'm returning to Vienna
I hope you have enjoyed my sessions
-Klaus
Well, a very good question: the last months were very stressful for me, and therefore I had to take priorities, and blogging wasn't the number one priority. But things are changing. By now I'm trying to post a lot more than in the past (which is very easy, as you can see from the history of my blog...)
Since february I'm employed as an software architect & consultant at EDS, an HP company in Vienna. I've been now around 6 weeks with EDS, and it's very amazing to seen how such a global player is working. And also the combination with HP will be very nice - just trust me Currently I'm doing a lot of SQL Server related stuff (my main topic, as you might already know), and also some things with .NET related technologies, evangelizing some great internal EDS technologies in Europe.
Here's also my upcoming conference schedule for the next weeks and months, where you can meet me face-2-face:
- DevWeek Conference London, UK (23. - 27. march): I'm doing one session about ADO.NET enhancements for SQL Server 2005/2008 and one session about distributed applications with the .NET framework 3.5
- European PASS Conference Neuss, Germany (23. - 24. april): I'm doing 2 session about SQL Server 2008: GEOGRAPHY data type and T-SQL enhancements
- Solid Quality Summit Vienna (June): I'm doing 4 (!) SQL Server related sessions together with my very good friends at Solid Quality Learning
See you!
-Klaus
A few minutes ago I finished my preparations for the upcoming DevWeek conference by the end of March in London, UK. One topic that I'll be talking about is building a fully distributed .NET application based on the .NET framework 3.5 technologies, WCF, WF, and WPF.
I'll show the following things:
- How to use the "Workflow Services" technology from the .NET framework 3.5
- How to store workflow definitions in a database, so that business analysts can change them according to their (changing) business requirements
- How to write a workflow designer, that can be used by the business analysts to change the workflow definitions that are stored in the database
- How to use the workflow monitor application from the SDK to monitor the current running and closed workflow instances
- How to use the TrackingProfileDesigner application from the SDK to configure tracking profiles for the running workflow instances
All in all: it's just an awesome sample how to use the current .NET framework 3.5 technology stack!
See you there!
-Klaus
Thanks for attending my sessions at the DevTeach conference in Toronto/Canada this week. As announced in all my sessions, you can download the whole material (Slides & Demos) for my sessions here:
- Message in a bottle: Service Broker from 1 to 100
- Service Broker Enhancements in SQL Server 2008
- Workflow driven Service Broker solutions
- SQL Server 2008 & the GEOGRAPHY data type
-Klaus
Thanks for attending my session about Team Build 2008 at the Team System Conference in Munich/Germany this week. As announced in my session, you can download the slides here. Enjoy it! -Klaus
Thanks for attending my sessions at the European PASS conference in Neuss/Germany this week. As announced in both of my sessions, you can download the whole material (Slides & Demos) for both sessions here. Enjoy it! -Klaus
Lernen Sie konzentriert das, was Sie zur BI Programmierung mit dem SQL Server 2008 benötigen.
Sichern Sie sich Ihren Technologievorsprung zur SQL Server 2008 BI Programmierung bereits diesen Sommer!
Zielpublikum: SQL Server 2000/2005 Programmierer
Termine: Montag 02.06.2008 bis Freitag 06.06.2008
Ort: Palais Strudlhof, Pasteurgasse 1, A-1090 Wien, Anfahrtsinformationen
Kosten: € 1.300,00 (im Betrag ist keine Umsatzsteuer enthalten)
Rabatt: Ab 3 Anmeldungen pro Firma wird Ihnen ein Rabatt von 10% pro Teilnehmer gewährt!
Es ist ein eigenes Notebook mitzubringen!
Tagesablauf:
- 09:00 - 13:00 Workshops
- 13:00 - 14.00 Mittagessen
- 14:00 - 18:00 Workshops
- Abends: Abendprogramm zum Ausklang des Tages
Das ergibt 5 x 8 Stunden gepowertes SQL Server 2008 BI Know-How zum Preis von nur € 1.300,00. Dieser Preis beinhaltet: - 5 Tage Power-Workshops zur SQL Server 2008 BI Programmierung
- Begrüßungskaffee/Tee
- Vormittagsjause
- 3-gängiges Mittagessen
- Nachmittagsjause
- Konferenzgetränke
Erfahren Sie alles was Sie zur SQL Server 2008 BI Entwicklung in der tagtäglichen Arbeit benötigen!
Warum teilnehmen?
Mit dem SQL Server 2008 stellt Microsoft eine umfassende Entwicklungsumgebung für Business Intelligence Anwendungen auf der Microsoft Plattform zur Verfügung. Das SQL Server 2008 BI Developers Summit ist daher eine ausgezeichnete Möglichkeit, sich schnell und praxisgerecht auf die BI Programmierung mit dem SQL Server 2008 ausbilden zu lassen. Bei dem Speaker (Willfried Färber, SQL Server MVP) handelt es sich um einen international anerkannten Experten, der sich bereits seit über 10 Jahren mit dem SQL Server und der Business Intelligence Entwicklung beschäftigt, und daher bereits umfangreiches Know-How aufbauen konnte und dieses in ausgezeichneter Qualität an die Teilnehmer weitergibt.
Top-Themen: - Theoretischer Hintergrund zu Data Marts und dem Data Warehouse
- Planung, Konzept und Entwurf eines Data Warehouse
- Einführung in die Integration Services
- Erstellen von Ladejobs für ein Data Warehouse
- Einführung in die Analysis Services
- Einführung in MDX
- Erstellung und Validierung eines Cubes
Weitere Informationen finden Sie unter der Event-Homepage. -Klaus
Thanks for attending one of my SQL Service Broker sessions at the DevWeek Conference in London last week. As announced in both of my sessions, you can download the whole material (Slides & Demos) for both SQL Service Broker sessions here. Enjoy it! -Klaus
Within the next months I'm doing some sessions at several conferences around the world: - DevWeek Conference (March 11 - March 14, London/UK)
- Building asynchronous, distributed Service Broker applications
- Scaleout scenarios with Service Broker
- Team System Conference (April 22 - April 24, Munich/Germany)
- European SQLPASS Conference (April 14 - April 16, Neuss/Germany)
- ADO.NET Enhancements for SQL Server 2005
- SQL Server 2008 and the GEOGRAPHY data type
- DevTeach Conference (May 12 - May 16, Toronto/Canada)
- Building asynchronous, distributed Service Broker applications
- Service Broker enhancements in SQL Server 2008
- Workflow driven Service Broker solutions
I'm looking forward to meet you at one of these events -Klaus
In the next 2 days I'm presenting 3 SQL Service Broker sessions at the DevTeach conference in Vancouver: - Message in a bottle: Service Broker from 1 to 100
- Workflow driven Service Broker Solutions
- Smart Client solutions with Service Broker
You can download here my slides and the samples. -Klaus
Heute und morgen halte ich 2 Vorträge zum SQL Service Broker bei den SQLdays in München: - Verteilte, zuverlässige Anwendungs-Architekturen mit dem SQL Service Broker
- Zuverlässige Smart Clients mit dem SQL Service Broker
Unter http://www.csharp.at/downloads/sqldays2007.zip finden Sie meine Präsentationen und Beispiele zum Download. Ich würde mich über Ihr Feedback zu den Vorträgen sehr freuen. -Klaus
Today I've started with the demo preparations for my upcoming spotlight session "Scaleout scenarios with Service Broker" for the SQLPASS Community Summit in Denver (September 18 - 21). In this session I'll show you how you can scale out an existing Service Broker application to any required size. In detail you'll see: - Load balancing: I'll take a SSB TargetService and implement load-balancing between different SQL Server machines
- Message Forwarding: After the first step (load balancing), the Initiator Service will communicate with a Message Forwarding Service, which forwards the request to the real TargetService. Because of this your internal network topology is completely isolated from your business partners.
- Configuration Notice Service: In the last and final step I'll implement a Configuration Notice Service for the Message Forwarder, so that the routes to the internal hosted services are created on demand. Therefore you have the chance to dynamically move your SSB services inside your environment.
What do you think? What kind of scale out scenarios would you will see in my session? Thanks -Klaus
In the last weeks Georg Drobny (Application Development Consultant from Microsoft Austria) and I presented a session about Visual Studio 2005 Team System at the Microsoft Big>Days. The Big>Days were on 4 different locations around Austria:
-Klaus
|