1 / 16

SQL Server 2005 SP2

SQL Server 2005 SP2. Israeli SQL Server User Group March 2005 Ami Levin amilevin@gmail.com. Redistribution of bugs. http://support.microsoft.com/default.aspx?scid=kb;en-us;921896 About 140 confirmed bugs fixed Most of them are concerned with AS.

wang-rosa
Download Presentation

SQL Server 2005 SP2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2005 SP2 Israeli SQL Server User Group March 2005 Ami Levin amilevin@gmail.com

  2. Redistribution of bugs • http://support.microsoft.com/default.aspx?scid=kb;en-us;921896 • About 140 confirmed bugs fixed • Most of them are concerned with AS

  3. Some interesting ones you might have suffered from and didn’t even know it… • 917905 • FIX: SQL Server 2005 performance may be slower than SQL Server 2000 performance when you use an API server cursor • 918276 • FIX: You notice additional random trailing character in values when you retrieve the values from a fixed-size character column or a fixed-size binary column of a table in SQL Server 2005 • 918882 • FIX: A query plan is not cached in SQL Server 2005 when the text of the hint is a large object • 919636 • FIX: Memory usage of the compiled query plan may unexpectedly increase in SQL Server 2005

  4. 919775 • FIX: The BULK INSERT statement may not return any errors when you try to import data from a text file to a table by using the BULK INSERT statement in Microsoft SQL Server 2005 • 919905 • FIX: A query may take longer to run in SQL Server 2005 SP1 than it takes to run in the original release version of SQL Server 2005 or in SQL Server 2000 • 920206 • FIX: System performance may be slow when an application submits many queries against a SQL Server 2005 database that uses simple parameterization • 920346 • FIX: SQL Server 2005 may overestimate the cardinality of the JOIN operator when a SQL Server 2005 query contains a join predicate that is a multicolumn predicate • 920347 • FIX: The SQL Server 2005 query optimizer may incorrectly estimate the cardinality for a query that has a predicate that contains an index union alternative • 922063 • FIX: You may notice a large increase in compile time when you enable trace flags 2389 and 2390 in SQL Server 2005 Service Pack 1

  5. 922438 • FIX: A query may take a long time to compile when the query contains several JOIN clauses against a SQL Server 2005 database • 923605 • FIX: A deadlock occurs and a query never finishes when you run the query on a computer that is running SQL Server 2005 and has multiple processors • 923849 • FIX: When you run a query that references a partitioned table in SQL Server 2005, query performance may decrease • 924344 • FIX: You may receive incorrect results when you use the bulk copy program command-line utility to import data in SQL Server 2005 • 924601 • FIX: A partitioned table may generate an inefficient query plan when you run a Transact-SQL query that uses the Top n clause on a partitioned table in SQL Server 2005 • 924807 • FIX: The restore operation may take a long time to finish when you restore a database in SQL Server 2005

  6. 925153 • FIX: You may receive different date values for each row when you use the GETDATE() function within a case statement in SQL Server 2005 • 926612 • FIX: SQL Server Agent does not send an alert quickly or does not send an alert when you use an alert of the SQL Server event alert type in SQL Server 2005 • 927643 • FIX: Some search results are missing when you perform a full-text search operation on a Windows SharePoint Services 2.0 site after you upgrade to SQL Server 2005 • 928537 • FIX: The full-text index population for the indexed view is very slow in SQL Server 2005

  7. Find and submit bugs and suggestions • http://connect.microsoft.com/

  8. Additional functionality • Maintenance plans • SSIS no longer required for using M.P. • Support for multiple schedules • Support for multi-server environments • Logging to remote servers • Clean up task in the wizard * Important note! • Must update pre SP2 cleanup tasks • Minimal interval was in days, SP2 adds hours • http://blogs.msdn.com/sqlrem/for details

  9. VARDECIMAL storage option • Only in Enterprise Edition (Developer/Evaluation) • Implemented at table level • Physical structural change in table and indexes. • Will lock the table exclusively for the duration of the change. • Run sp_db_vardecimal_storage_format to enable the database, and then sp_tableoption, to enable for the appropriate tables. • Cannot be used for system databases • http://msdn2.microsoft.com/en-us/library/bb326755.aspx

  10. Logon Triggers USE master; CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' GRANT VIEW SERVER STATE TO login_test; CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK; END; • http://msdn2.microsoft.com/en-us/library/bb326598.aspx

  11. Additional functionality • Common criteria certification • http://msdn2.microsoft.com/en-us/library/bb153837.aspx • Sqllogship application • http://msdn2.microsoft.com/en-us/library/bb283327.aspx • SMO enhancements • Table.CheckIdentityValue() • Column.AddDefaultConstraint() • Replication enhancements • In Enterprise Edition, you can now initialize subscriptions to snapshot and transactional publications by using a database snapshot. • Merge replication now provides a stored procedure that regenerates the triggers, stored procedures, and views that are used to track data changes. • See sp_vupgrade_mergeobjects.

  12. Compatibility issues • Windows Vista • Office 2007 • SharePoint Services 3.0 • Oracle data sources that run on version 9.2.0.3 or later for reporting services • Hyperion System 9.3 BI+ Enterprise Analytics data source

  13. Tools enhancements • Management studio custom reports • Scripting • Generate script wizard • More control on scripting options • Tools -> Options -> Scripting tab • Graphical show plan improved spacing • Linked server dialog box “Test Connection” • For a full list see • http://msdn2.microsoft.com/en-us/library/bb283536.aspx

  14. Separate features downloads • SQL Server express SP2 • http://go.microsoft.com/fwlink/?linkid=64064 • Updated Books On Line • http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx • SQL Server 2005 feature pack Feb. 2007 • http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en • Updated samples and sample databases • http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en • AdventureWorksLT Diet version

  15. Best practices analyzer CTP • Analyzes SQL Server databases and server configurations with a set of predefined “best practices” rules and generates recommendations. • http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

  16. Performance dashboard • You are not supposed to see this • Official announcement will be in a few weeks so don’t tell anyone I showed it to you… • Quest, Symantec etc. beware…  Demo

More Related