SQL Server 2008 Advanced Technologies

1. SQL Server 2008Advanced Technologies Bratislava, 13.5.2008 Karol Papaj SQL Server MVP kpapaj@solidq.com

2. WHO WE ARE • Solid Quality™ Mentors are more than 100 of the world’s top technical experts who specialize in assuring client successusing integrated Microsoft technologies. • http://www.SolidQ.com • Articles:http://www.solidq.com/na/TechArticles.aspx • Blogs:http://www.solidq.com/na/OurBlogs.aspx • 42 Microsoft MVPs • 7 Regional Directors • Mentors located in over 20 countries

3. SQL Server Fitness Check • Worried about your database performance? • Doubts about your disaster recovery plans? • Concerned about your data security? YES ? Take the SQL Server Fitness Check! • Consultancy package designed to answer your questions • There is no one-size-fits-all solution. Solid Quality Mentors provides a customized approach to view at every aspect of your SQL Server environment and to understand your specific needs. • More information: www.SolidQ.com

4. Agenda • Windows 2008 + SQL Server 2005/2008 • Novinky SQL 2008 pre administrátorov • Novinky SQL 2008 pre vývojárov • SQL 2008 DataWarehousing

5. Windows Server 2008 + SQL Server • Vysoká dostupnosť • Self Healing NTFS • Failover cluster (disk access, storage ...) • Bezpečnosť • BitLocker Drive Encryption • Network Access Protection • Výkonnosť • Memory Manager optimization (1 MB reads/writes, Large I/O on mapped files, ...) • Optimalizácia context switching (I/O Port Completion, Thread scheduling ...) • NUMA optimization • TCP/IP optimization

6. http://blogs.msdn.com/buckwoody/archive/2008/05/05/windows-2008-and-replication.aspxhttp://blogs.msdn.com/buckwoody/archive/2008/05/05/windows-2008-and-replication.aspx

7. SQL Server pre administrátorov • Policy Based Management • Resource Governor • Performance Studio • Transparent Data Encryption • Audit • Extended Events • Database Mirroring (page recovery, log compression)

8. Policy Based Management • Nástroj na správu SQL serverov založený na využívaní politík • 3 hlavné komponenty: • Správa politík • Ad-hoc administrácia • Automatizovaná administrácia • Scenáre nasadenia: • Dodržiavanie firemnej názvoslovnej konvencie • Dodržiavanie konfigurácie SQL inštancií (xp_cmd_shell, ...)

9. Resource Governor • Nástroj na správu zdrojov SQL (RAM, CPU) v reálnom čase • Navrhnutý na: • Konsolidáciu viacerých aplikácií na spoločnom servri • Zabezpečenie dostupnosti servera (resource intensive query) • Nastavenie priority podľa typu záťaže (OLTP vs. DW) • Obmedzenia súčasnej verzie: • Riadenie zdrojov RAM, CPU • Len pre DB Engine (nie SSAS, SSIS, SSRS) • OLTP (?)

10. Performance Studio • Nasledovník SQL Server Performance Dashboard • „DataWarehouse“ na zbieranie, analýzu a ukladanie diagnostických informácií SQL • Kľúčové komponenty: • Data provider (Profiler, Perfmon, T-SQL) • Collector Type • Collection Item • Collection Set • Collection Mode

11. Transparent Data Encryption • Šifrovanie I/O DB a LOG súborov v reálnom čase • Šifrovanie na úrovni stránok • Podporované AES/3DES algoritmy • Backup šifrovanej db obsahuje šifrované dáta • TempDB je šifrovaná ak je šifrovaná jedna db v inštancii • Je možné používať DB compression spolu s TDE ? • FILESTREAM ?

12. Audit • Výkonnáinfraštruktúra pre auditovanie • Využíva infraštruktúru Extended Events • Výkonnejší ako SQL Profiler, skriptovateľný T-SQL • Audituje: • 35 auditovacích skupín (Succesful_Login_Group, Logout_Group, ...) • INSERT, UPDATE, DELETE, REFERENCES, EXEC a .... SELECT ! • Výstup: • Windows App/Sec log, súbor

13. SQL Server Extended Events • Infraštruktúra Extended Events podporuje zbieranie, filtrovanie a reakcie na udalosti generované procesmi servera • Spolu s využívaním Event Tracing for Windows (ETW) je možné korelovať DB aplikáciu s Windows • Výhody: • Integrácia s Windows udalosťami • Low overhead • User configurable • ...

14. Database Mirroring • Database Mirroring poskytuje vysokú dostupnosť posielaním záznamov z transakčného logu na standby server • Výhody DB Mirroring: • Ochrana dát • Zvýšenie dostupnosti riešenia v prípade katastrofy • Zvýšenie dostupnosti produkčných riešení počas upgradov • SQL Server 2008 Database Mirroring: • Automatic page repair. • Improved performance. • Enhanced supportability

15. SQL Server 2008 pre vývojárov • Nové dátové typy (date, datetime2, Filestream...) • T-SQL (MERGE, Grouping Sets, CDC, Table-Valued parameters, row constructors) • Filtered indexes & statistics • Sparse columns

16. Dátové typy • Data Types • Improved datetime* • date, time • smalldatetime, datetime, datetime2 • datetimeoffset • HierarchyID • Large UDT's • Spatial Data • geometry, geography • FILESTREAM attribute • Integrated Full-Text Search

17. Dátumové dátové typy

18. T-SQL • MERGE • Jedným príkazom vkladá alebo aktualizuje dáta v tabuľke podľa join podmienky so zdrojovou tabuľkou • Podmienky • WHEN MATCHED • Záznam existuje v obidvoch tabuľkách • WHEN TARGET NOT MATCHED • Záznam existuje len v zdrojovej tabuľke • WHEN SOURCE NOT MATCHED • Záznam existuje len v cieľovej tabuľke

19. MERGE MERGE INTO targettable t USING sourcetable s ON t.pk = s.pk WHEN MATCHED AND s.col1 = 0 THEN DELETE WHEN MATCHED AND s.col1 <> 0 THEN UPDATE SET t.col1 = s.col1 WHEN TARGET NOT MATCHED THEN INSERT (pk,col1) VALUES(s.pk,s.col1);

20. Grouping Sets • Rozšírenie GROUP BY • Precíznejšie agregácie ako s WITH ROLLUP/WITH CUBE • Generuje jeden resultsets • Ekvivalent UNION ALL rôzne zoskupených záznamov • Efektívnejší a rýchlejší spôsob ako UNION ALL

21. Table-Valued Parameters • Nový typ parametra v SQL 2008 • Umožňuje zaslanie skupiny viacerých záznamov do procedúry alebo funkcie ako parameter • Výhody: • Flexibilnejšie riešenie • Jednoduchší programovací model • Umiestnenie Business logic v jednej procedúre/funkcii • Umožňuje nepoužívať Temporary tables • Príkaz sa nerekompiluje

22. Table-Valued Parameters CREATE TYPE Emailaddresses AS TABLE (email nvarchar(50) Primary Key, display nvarchar(50)) GO CREATE PROCEDURE pr_storemail @tab Emailaddresses READONLY AS --PROCESS SELECT email, display FROM @tab GO --USE DECLARE @src Emailaddresses INSERT INTO @src SELECT emailaddress, lastname FROM person.contact EXEC pr_storemail @src

23. Row Constructors • Umožňuje jedným INSERT príkazom vložiť viacej záznamov • Možné použiť aj ako table source

24. Row Constructors • CREATE TABLE name_table (name varchar(20), age int);goINSERT INTO name_table VALUES ('Bob', 54), ('Mary', 30), ('Sam', 15), ('Buddy', 9); • SELECT n.name, n.age, tab.speciesFROM name_table nJOIN (   VALUES ('Bob', 'person'), ('Mary', 'person'), ('Sam', 'cat'), ('Buddy', 'cat')) tab (name, species) ON n.name = tab.name

25. Sparse Columns • Stĺpce s optimalizovaným ukladaním NULL hodnôt • Odporúčanie používať pri úspore miesta od 20-40 percent • Možné využívať spolu s FILTERED INDEX

26. So I was playing today with the sparse column implementation in the latest SQL 2008 CTP, and I was specifically looking to see how this thing would work with the query processor.  I wanted to know how much information is exposed for the QP to make smart decisions when this functionality is used in complex queries with cost-based plan choices.  If the QP doesn't have the information, then sometimes the query plans will be sub-optimal because, well, garbage-in garbage-out.  While the SQL Server QP does a tremendous job at making complex plan choices compared to some of the other commercial alternatives, there are still limits on what the Optimizer can model in a reasonable amount of time.  As such, there are seams where the product tends to not work as well as one would hope.  This will always be true.  While I suppose that will also keep me employable, it is useful to understand those limits because it will help you know where to look or, if it's really hard, when to ask for help.The SQL Server QP knows a couple of things about the data stored in a table in the storage engine:1. How many physical pages it uses2. How many rows it has in it (approximately)3. Single-column statistics over a sample of the data4. A basic notion of column interdependance to help in estimating queries with multiple scalar predicates.From 1 and 2 it can derive the average row width.  That's useful for determining things like "how big will my sort be" if the query needs to sort.  That's a good thing - it leads to reasonable estimates for many choices in the QP.So let's add sparse columns into the mix.  Sparse columns are useful for data with lots of NULLs.  Often this is a result of a non-traditional third-normal form database problem or, perhaps someone who is not a database person not really trying to make something into a database problem early enough in its lifecycle.  The point is that commercial database systems have a sweet spot around handling data sets with known (and small) sets of columns that can be stored in tables.  There is a TON of expressiveness available in query processors that manipulate this data because this format of data is better supported than other formats.None of this really means that your problem is going to easily fit into a nice third-normal form system.  Often there are legacy or performance concerns that push an application away from that sweet spot.  Over time, various technologies have tried to bridge that gap (property tables, XML, and object-relational mappings).  Each of them have their own reasons to be, and I don't want to get into them in depth in my post.  I'm going to talk about how the QP deals with these from a modeling perspective.I built two examples to explore how SQL Server 2008 reasons about sparse columns.  One example creates lots of traditional, nullable float columns while the other is exactly the same except that it uses the sparse attribute.A few things I learned immediately:1. Sparse columns don't change the maximum number of columns you can create in a table.  On the surface, this seems unfortunate, since it will limit the kinds of applications that can use the feature.  2. It does seem to use less space per row.  This isn't hard, as the row format for SQL Server has a null bitmap and also needs 2 bytes per column to store the variable offset pointers.create table sp1(aaa int) create table sp2(aaa int) declare @i int set @i=0 while (@i < 990) begin declare @sql nvarchar(400); declare @s nvarchar(20); set @s = @i; set @sql = 'alter table sp1 add col' + @s + ' float sparse' exec sp_executesql @sql set @i=@i+1 end declare @i int set @i=0 while (@i < 990) begin declare @sql nvarchar(400); declare @s nvarchar(20); set @s = @i; set @sql = 'alter table sp2 add col' + @s + ' float' exec sp_executesql @sql set @i=@i+1 enddeclare @i int set @i=0 while @i < 20000 begin insert into sp1(col2) values (123.4) set @i=@i+1 end declare @i int set @i=0 while @i < 20000 begin insert into sp2(col2) values (123.4) set @i=@i+1 endIf we run "set statistics io on" and then run "select * from sp1" and "select * from sp2", you'd like to see some difference in IOs:sp1:(20000 row(s) affected)Table 'sp1'. Scan count 1, logical reads 86, physical reads 0, read-ahead reads 80, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.sp2:(20000 row(s) affected)Table 'sp2'. Scan count 1, logical reads 20000, physical reads 1, read-ahead reads 19978, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Well, that's good - the sparse format on largely sparse data saves space.  We can confirm that with a quick look into the system tables:SELECT o.name AS table_name, au.type_desc, au.used_pages FROM sys.allocation_units AS au JOIN sys.partitions AS p ON au.container_id = p.partition_id JOIN sys.objects AS o ON p.object_id = o.object_id WHERE o.name in (N'sp1', N'sp2')table_name                                                                                                                       type_desc                                                    used_pages-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ --------------------sp1                                                                                                                              IN_ROW_DATA                                                  87sp1                                                                                                                              ROW_OVERFLOW_DATA                                            0sp2                                                                                                                              IN_ROW_DATA                                                  20001(3 row(s) affected) We've now confirmed that we actually do have fewer pages.  This is also good.Now let's see how far into the QP this extends.  Does the QP model the costs for these two queries differently?SP1 TotalSubtreeCost: 0.08824496SP2 TotalSubtreeCost: 14.83936 Sparse Columns • create table sp1(aaa int) create table sp2(aaa int) declare @i int set @i=0 while (@i < 990) begin declare @sql nvarchar(400); declare @s nvarchar(20); set @s = @i; set @sql = 'alter table sp1 add col' + @s + ' float sparse' exec sp_executesql @sql set @i=@i+1 end declare @i int set @i=0 while (@i < 990) begin declare @sql nvarchar(400); declare @s nvarchar(20); set @s = @i; set @sql = 'alter table sp2 add col' + @s + ' float' exec sp_executesql @sql set @i=@i+1 enddeclare @i int set @i=0 while @i < 20000 begin insert into sp1(col2) values (123.4) set @i=@i+1 end declare @i int set @i=0 while @i < 20000 begin insert into sp2(col2) values (123.4) set @i=@i+1 endIf we run "set statistics io on" and then run "select * from sp1" and "select * from sp2", you'd like to see some difference in IOs:sp1:(20000 row(s) affected)Table 'sp1'. Scan count 1, logical reads 86, physical reads 0, read-ahead reads 80, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.sp2:(20000 row(s) affected)Table 'sp2'. Scan count 1, logical reads 20000, physical reads 1, read-ahead reads 19978, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Well, that's good - the sparse format on largely sparse data saves space.  We can confirm that with a quick look into the system tables:SELECT o.name AS table_name, au.type_desc, au.used_pages FROM sys.allocation_units AS au JOIN sys.partitions AS p ON au.container_id = p.partition_id JOIN sys.objects AS o ON p.object_id = o.object_id WHERE o.name in (N'sp1', N'sp2')table_name                                                                                                                       type_desc                                                    used_pages-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ --------------------sp1                                                                                                                              IN_ROW_DATA                                                  87sp1                                                                                                                              ROW_OVERFLOW_DATA                                            0sp2                                                                                                                              IN_ROW_DATA                                                  20001(3 row(s) affected) • We've now confirmed that we actually do have fewer pages.  This is also good.Now let's see how far into the QP this extends.  Does the QP model the costs for these two queries differently?SP1 TotalSubtreeCost: 0.08824496SP2 TotalSubtreeCost: 14.83936

27. Filtered Indexes & Indexes • Optimalizovaný non-clustered index • Využíva filter predicate (WHERE clause) na vytvorenie indexu na časti záznamov z tabuľky • Výhody: • Zlepšená výkonnosť query • Znížená náročnosť na údržbu • Znižuje náklady na storage

28. Filtered Indexes & Statistics • create database t1 • use t1 • drop table t1 • create table t1(col1 int, col2 nchar(2000), col3 time) • create index i1 on t1(col1) where col1 > 5 and col1 < 20 • declare @p int • set @p =0 • while @p < 20000 • begin • insert into t1(col1) values (rand()*10000) • set @p=@p+1 • end • select * from t1 where col1 > 5 and col1 < 20  • select * from t1 where col1 > 5 and col1 < 10

29. SQL 2008 DataWarehousing • Relačný DataWarehouse • Analysis Services • Reporting Services • Integration Services

30. Relačný DataWarehouse • Star Join Optimization • Data / Backup Compression • Change Data Capture

31. Star Join Optimizations • Použitie • Decision support queries • Multiple dimensions with fact table • Ciele • Zlepšenie výkonnosti • Žiadna špeciálna query syntax • Žiadne zvýšené nároky na údržbu • Automaticky aktivované (no hints)

32. Star Join Optimizations • Jedna z najčastejšie typov používaných query v DW • Náročná na zdroje • Optimalizácia Query Processora • Detekcia star a snowflake schémy • Automatická identifikácia fact tabuliek a dimenzií • Metóda bitmap filter a hash join • Vyžaduje multi-procesorové systémy

33. Star Join Optimization

34. Data/Backup Compression • Table / Index Compression • Row Compression • Page Compression • SQL 2008 Enterprise / Developer Edition • Data Compression + Transparent Data Encryption ?

35. Backup Compression • SQL Server Customer Advisory Team

36. Uncompressed vs. Compressed Backup

37. Analysis Services • Improved Manageability • Dynamic Management Views • Backup/Restore • Dimension Designer & Best Practices Alert • Improved Performance • Block Computation

38. Reporting Services • Zlepšená výkonnosť • Škálovateľnosť • Nie je potrebné IIS (http.sys) • Konfigurácia pamäte • Zlepšená visualizácia (Dundas) • Word / Excel Renderer • Report Designer

39. Integration Services • Persistent Cache Lookup • Performance • Data Profiling