|
After a long and much anticipated wait SQL Server 2005 has been released to manufacture (RTM). Although the official launch of the product is not until the 7th of November the final build of the product was announced on Friday the 29th of October. For MSDN subscribers the final build of the Developer, Enterprise, Express, Standard and Workgroup Editions are all now available for download. If you are thinking of holding back from deploying SQL Server 2005 in your production environment until Service Pack One is released then below are some statistics from some of the worlds largest companies that have already deployed SQL Server 2005: Barnes & Noble - 3TB Data Warehouse on a 64-Bit machine Experience - Largest Analysis Service cube with 15million rows and 300 attributes NASDAQ - 5,000 transactions /second and over 100, 000 queries/day on a trade summary system Mediterranean Shipping - 50 million transactions/day running their most mission critical shipping application 24x7 Xerox - 7 million transactions /day The SQL Server 2005 launch events kick off in San Francisco on the 7th of November 2005 and will continue in Australia during late November and early December. To be a part of the launch events you can register at http://www.microsoft.com/australia/readylaunch/itpro.aspx. Should you have any suggestions or feedback on how we can improve the newsletter or content that you would like covered, please do not hesitate to email newsletter@wardyit.com. Happy Reading Peter Ward WARDY IT Solutions Chief Technical Architect Phone: +61 7 3856 5411 Mobile: +61 0 403 177 761 |
|
In this Issue: SQL Server 2000 Performance Analysis Tools SQL Server beats the TPC $1.00/transaction barrier Understanding TEMPDB SchemaSpy SQL Server vs Oracle New SQL Server Certifications Dynamic Management Views SQL Server Fiber Mode DMO of the Month T-SQL of the Month SQL Server Tip of the Month |
|
SQL Server 2000 Performance Analysis Tools Microsoft has released two more of the tools that have been developed internally for the Product Support Services (PSS) Team to identify performance issues with SQL Server. read80Trace can be used to process trace files that are generated by SQL Server 2000 in order to produce a summary of the details in the trace file. The data generated by read80Trace can be stored in a normalised database allowing you can perform your own queries on it. The OSTRESS utility is a multithreaded ODBC-based query utility that can be used for stress testing SQL Server 2000. You can pass either an RML file produced by read80Trace or a standard go-delimited SQL script file as the query in order to perform a specific type of stress load on the server. http://support.microsoft.com/default.aspx?scid=kb;en-us;887057 SQL Server beats the TPC $1.00/transaction barrier The Transaction Processing Performance Council (TPC) has recently published that SQL Server 2005 has broken the $1 price-per-transaction ($/tpmC) barrier. Basically this means that 38,622 transactions per-second were processed under TPC guidelines (http://www.tpc.org/tpcc/detail.asp) on a single Dual-Core Intel® Xeon™ processor 2.80 GHz 2+2M server costing US$38,028. http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=105092601 Understanding TEMPDB Kimberly Tripp (http://www.sqlskills.com/blogs/kimberly) from SQL Skills has written a great article on how TEMPDB is used by SQL Server and how to optimise TEMPDB so that it does not become a bottleneck. The article also discusses some of the confusion that surrounds the use of Table Variables vs Temp tables identifying when each particular method should be used. http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=8be9d388-b354-429f-8c98-e45989a4bebe SchemaSpy SchemaSpy is a Java-based tool that analyses the metadata of a schema in a database and generates a visual representation in a browser. It supports the majority of major DBMS players including SQL Server. SchemaSpy supports browsing the schema through a graphical representation of the relationships and has an interesting feature called ‘Anomalies’ which identifies configurations that may not be quite right with the schema. http://schemaspy.sourceforge.net/ SQL Server vs Oracle There continues to be a slug match between SQL Server and Oracle regarding the performance of each DBMS. Oracle continues to state that it leads the market in regards to scalability and performance especially in the Very Large Database space. The following article is an independent comparison of the strengths and weaknesses of both the VLDB and OLTP features of each product. http://www.wisdomforce.com/dweb/resources/docs/MSSQL2005_ORACLE10g_compare.pdf New SQL Server Certifications Recently Microsoft announced a new set of certifications for IT professional working with SQL Server Microsoft Certified Technology Specialist: SQL Server 2005, Microsoft Certified IT Professional: Database Developer, Microsoft Certified IT Professional: Database Administrator and Microsoft Certified IT Professional: Business Intelligence Developer. The MCDBA for SQL Server 2000 will continues to be recognized by Microsoft and you do not need to do anything to maintain it. There is an upgrade option for SQL Server 2005 which is available to those currently certified as MCDBA’s. The upgrade exam will be available early-mid next year which will allow you to upgrade to the new MCITP: Database Administrator credentials after earning the prerequisite Technology Specialist credentials. For more details regarding what this means to those with existing certifications refer to http://www.microsoft.com/learning/mcp/newgen/faq/ Dynamic Management Views Dynamic Management Views (DMVs) have been introduced in SQL Server 2005 to allow you to see what is happening under the covers of SQL Server. DMV’s have been designed to be used instead of the system tables and the various other functions that were used in SQL Server 2000 to help look under the hood. For more information on the data that can be obtained using the DMV’s refer to http://msdn2.microsoft.com/en-us/library/ms188754.aspx SQL Server Fiber Mode Recently we have seen several sites were the use of Windows NT fiber mode has been enabled. Fiber mode can help to reduce context switching between threads and switches between user mode and kernel mode on the CPU. However, it is generally recommended that fiber mode is not enabled. Ken Henderson has written a great article which explains the reason that fiber mode isn't generally recommended. http://msdn.microsoft.com/library/?url=/library/en-us/dnsqldev/html/sqldev_02152005.asp DMO of the Month: Often there is the need to use a SELECT or INSERT column list for a table that has a long list of columns. Rather then copying each of the columns and then adding a comma between each column the VBScript code snippet below can be used to generate a column list that can simply be copied and pasted into Query Analyzer. Option Explicit Const OUTPUT = "c:\output.txt" Const ForWriting = 2 Dim oSQLServer Dim oFSO Dim oFSOWrite Dim oShell Dim col Dim line Dim ServerName Dim Database DIm Table Set oSQLServer = CreateObject("SQLDMO.SQLServer") Set oFSO = CreateObject("Scripting.FileSystemObject") Set oShell = CreateObject("WScript.Shell") Set oFSOWrite = oFSO.OpenTextFile(OUTPUT, ForWriting, True) ' Additional validation should be added to this input ServerName = InputBox("Enter the SQL Server instance to connect to", "www.wardyit.com") Database = InputBox("Enter the Database", "www.wardyit.com") Table = InputBox("Enter the Table to generate a column list for", "www.wardyit.com") oSQLServer.LoginSecure = True oSQLServer.Connect ServerName For Each col In oSQLServer.Databases(Database).Tables(Table).Columns line = Line & col.name & ", " Next line = mid(line, 1, instrrev(line, ",") - 1) oFSOWrite.Write line oFSOWrite.Close ' For details regarding the Windows Script Host run method refer to ' http://shrinkster.com/91z oShell.Run "notepad.exe " & OUTPUT, 3 oSQLServer.DisConnect Set oShell = Nothing Set oFSO = Nothing Set oSQLServer = Nothing |