Archive
Sydney SSUG Meeting (Nov 2007)
Sydney SQL Server User Group Meeting for November 2007
Victor Isakov will be presenting at the Sydney SQL Server User Group for November 2007.
Session 1: SQL Server 2005 Locking Architecture
Performance problems can be related to concurrency issues, as opposed to hardware/resource issues. Consequently it is important for both the developer and the DBA to understand how locking works. In this session Victor will examine SQL Server’s locking architecture and how to configure and troubleshoot locking.
Session 2: SQL Server 2005 DBA Best Practices
In the second session Victor is going to draw from his expertise in the field and present a number of best practices in the areas of database engine configuration, database configuration, database consistency checks and disaster recovery. A very practical session for DBAs of all levels.
SQL Server 2005 PHP Driver
SQL Server 2005 Driver for PHP Community Technology Preview (October 2007)
Microsoft has released a new SQL Server 2005 Driver for PHP.
The SQL Server 2005 Driver for PHP Community Technology Preview (CTP) download is available to all SQL Server users at no additional charge. The SQL Server 2005 Driver for PHP is a PHP 5 extension that allows for the reading and writing of SQL Server data from within PHP scripts. The extension provides a procedural interface for accessing data in all editions of SQL Server 2005 and SQL Server 2000.
SQL Server 2005 Driver for PHP Community Technology Preview (October 2007)
-T1118
Trace Flag -T1118
Lorem ipsum.
Trace flag -T1118 applies to SQL Server 2000 SP4 and SQL Server 2005.
8 April 2008
Mainstream Support for SQL Server 2000 Retires on 8 April 2008
Time is running out…
Lorem ipsum
According to Microsoft mainstream support for SQL Server 2005 will be retired on the 8th April 2008.
| Products Released | General Availability | Mainstream Support Retired | Extended Support Retired |
| SQL Server 2000 64-bit Edition | 30 November 2000 | 8 April 2008 | 9 April 2013 |
| SQL Server 2000 Developer Edition | 30 November 2000 | 8 April 2008 | 9 April 2013 |
| SQL Server 2000 Enterprise Edition | 30 November 2000 | 8 April 2008 | 9 April 2013 |
| SQL Server 2000 Standard Edition | 30 November 2000 | 8 April 2008 | 9 April 2013 |
| SQL Server 2000 Workgroup Edition | 1 June 2005 | 8 April 2008 | 9 April 2013 |
| SQL Server 2000 Windows CE Edition 2.0 | 16 December 2002 | 8 January 2008 | 8 January 2013 |
The following table describes the difference between Mainstream and Extended Support:
| Support Provided | Mainstream Support Phase | Extended Support Phase |
| Paid support (per-incident, per hour, and others) | X | X |
| Security update support | X | X |
| Non-security hotfix support | X | Requires extended hotfix agreement, purchased within 90 days of mainstream support ending. |
| No-charge incident support | X | |
| Warranty claims | X | |
| Design changes and feature requests | X | |
| Product-specific information that is available by using the online Microsoft Knowledge Base | X | X |
| X | X |
Furthermore, Microsoft does not issue further service packs during the Extended Support Phase.
The Little Computer Running SQL Server
The Little Computer Running SQL Server Underneath the Receptionist’s Desk…
I am continually surprised by the "lack of respect" or "lack of awareness" by organizations when it comes to SQL Server and the solutions they have implemented based on its technology. Surely the most important component of any organization is their data, their intellectual property. Which invariably is kept inside a database. And more often than not, in a SQL Server database.
So why don’t companies proactively ensure that these databases are "performant", are available, have been configured to industry best-practice? Why have there been no disaster recovery plan designed in the case of hardware failure or accidental data deletion? Why have they not evaluated the cost of implementing a high-availability solution versus the cost to the business if that "little computer running SQL Server underneath the receptionist’s desk" fails?
Organizations have no problems with insuring against fire or ensuring that their computers and telephones work efficiently. They have no problems with engaging a fire inspector to make sure that the fire extinguishers work. Or invest in soft skills training to ensure their sales team performs well. Yet they do not seem to want to ensure that their backup plans work, or that their databases perform well.
Furthermore, they are generally quite happy to let any contractor who puts "have experience with SQL Server" on their resume to configure and maintain their SQL Server environment. They certainly would not let anyone inspect a fire extinguisher! Nor let anyone who put "I can sell" coach their sales staff. I would hope ;o)
Let me illustrate my point by two engagements that I performed very recently.
In the first instance I was asked to troubleshoot performance problems for an international transport company. They had a SQL Server based solution that was being used across Australia, running on a quad-processor system with 8GB of RAM. Performance had been degrading over the last couple of months, to the point that they had to reboot the server every so often, typically during production hours, as it had simply "hung". (I might briefly add that they had not kept the error log files after each reboot, thus deleting vital troubleshooting information.) Otherwise the processor utilization maxed out at 100% consistently from 11AM to 2PM during week days. This amounted to a substantial loss in productivity and ultimately, I suspect a poor customer experience.
My initial reaction was "Why have we gotten to this stage? These problems could have been solved a month earlier, upon the problems first materializing."
In the second instance, I was asked to perform a "health-check", as they are commonly called, for a national media company. What had precipitated this health-check? Well as I discovered in the preliminary meeting, someone had accidentally deleted some data. When the IT staff attempted to perform a restore they discovered that their backups had "inexplicably stopped working" 3 months prior. Subsequently there was no confidence in how any of their SQL Server instances had been configured, how the backup strategy had been implemented, and so on.
Again, my initial reaction was "Why did it take such an occurrence for the organization to ensure that their SQL Server solution was optimal and robust?”
It would seem that the industry generally does not have a "healthy respect" or "awareness" of SQL Server’s technology and how to best implement a solution based on that technology. Have Microsoft lulled us into a false sense of security by espousing how easy SQL Server is to maintain and how well it performs "out of the box"? As I have often stated, Sybase DBAs seem to know Sybase better, and Oracle DBAs seem to know Oracle better compared to SQL Server DBAs. SQL Server has the lion’s share of the database engine market, and a corresponding demand for SQL Server "experts". The natural consequence of this demand is potentially hiring junior SQL Server DBAs by organizations (or forgoing them altogether) due to a lack of senior DBAs, or SQL Server DBAs not having enough time to skill themselves up in the latest technology or best practices.
Perhaps the Australian mantra "She’ll be right mate" (or your local equivalent) will suffice, until that "little computer running SQL Server underneath the receptionist’s desk" fails!
SQL Server 2005 SP2 Cumulative Update 5
Cumulative Update Package 5 for SQL Server 2005 Service Pack 2
Microsoft has announced cumulative update package 5 for SQL Server 2005 Service Pack 2. It "will be released in the December 2007 timeframe".
Becoming an Evangelist
Becoming an Evangelist – Get the inside scoop from not one but two U.S. Developer Evangelists!
A good friend Lynn Langit was recently part of a web-cast on becoming an Evangelist. Along with Asli Bilgin they answered the question “What the heck is an Evangelist really?” and shared their perspectives on what it’s like to be an Evangelist and a woman in a technical profession.
Becoming an Evangelist – Get the inside scoop from not one but two U.S. Developer Evangelists!
Configuring tempdb for SQL Server 2005
Configuring the tempdb System Database for SQL Server 2005
The tempdb system database in SQL Server 2005 plays a much more important role than in earlier versions of SQL Server. Consequently it is important to ensure that it has been correctly sized and configured.
The tempdb system database is still used to store intermediate results for queries, temporary tables, table variables, table-valued functions and cursors. However, there have been a number of changes in SQL Server 2005.
The tempdb system database is explicitly used by a number of SQL Server 2005’s features, including:
- Database Mail
- Event Notifications
- Multiple Active Result Sets (MARS)
- Online Index Rebuild
- Query Notifications
- Read Committed Snapshot Isolation (RCSI)
- Row-Versioning
- Service Broker
- Snapshot Isolation
However, unbeknownst to a lot of DBAs, a number of SQL Server 2005 features now use the tempdb system database implicitly, including:
- Index Creation
- Triggers
- DBCC CHECK commands
This was not necessarily the case in SQL Server 2000. As you can see, you will mostly likely be utilizing the tempdb system database more heavily after performing an upgrade to SQL Server 2005. Potentially your tempdb system database will become more of a bottleneck if it is not correctly sized and configured.
Compounding this potential bottleneck is the fact that SQL Server 2005 has to modify more system tables whenever it creates an object in the tempdb system database (sysallocunits, syshobts, syshobtcolumns, sysrowsets, sysrowsetcolumns).
Another factor is that whenever an object needs to be created SQL Server 2005 has to modify the internal allocation pages (the Page Free Space (PFS) page, the Global Allocation Map (GAM) page and in particular the Shared Global Allocation Map (SGAM) page) to allocate the mixed and/or uniform extents. Thus there is potentially contention on the SGAM page.
General Best Practices
First and foremost you should determine how heavily your SQL Server 2005 instance will be utilizing the tempdb system database. There is no substitute for determining and consequently knowing how the tempdb system database is being utilized. This can be achieved by talking to your database developers and/or software vendor.
It can be as simple as going through the checklist of features that use the tempdb system database that I described above. Don’t hesitate to ask what your software vendor recommends. (Although in my experience software vendors usually have no idea about the database engine that their product is using.) Otherwise it is a simple matter of monitoring the tempdb system database through the SQL Server 2005 tools. I will discuss what to monitor at the end of this article.
Otherwise there are a number of tenets that you can apply to maximize the performance of your tempdb system database:
- Place the tempdb system database on a disk drive separate from your user database files.
- Place the tempdb system database on a dedicated disk drive.
- Place the tempdb system database on a faster disk drive. (Say a 15,000rpm disk drive instead of a 10,000rpm disk drive.)
- Separate the log file from the data file for your tempdb system database.
- Consider using a solid state drive (SSD) for the tempdb system database. A quick search on the web indicated that a number of commodity hardware vendors have, or will have in the near future, a number of SSDs out in the market.
- Consider placing the tempdb system database on the local drives instead of on the SAN.
- Avoid placing the tempdb system database on a RAID 5 partition due to the write I/O penalty.
- Ensure that you have configured Instant File Initialization under Windows 2003. This will improve the performance of the automatic growth of databases, including the tempdb system database.
But again, remember, that you have to determine that your SQL Server 2005 instance is heavily utilizing the tempdb system database to realize the maximum performance benefits.
Optimal Configuration of the tempdb system database
The default configuration of the tempdb system database for SQL Server 2005 (8MB in size) is sub-optimal. I suppose Microsoft’s main consideration for its main configuration was to ensure that it is consistent across all the SKUs that exist, from Express Edition to Enterprise Edition. They would expect DBAs to configure it correctly in the enterprise space.
There are a number of simple tenets that you can follow for the optimal configuration of the tempdb system database:
- Pre-allocate an appropriate size to the data and log files. (Ignore Microsoft’s lame recommendation in SQL Server 2005 BOL!) The goal is to avoid the SQL Server 2005 database engine having to invoke automatic growth. (Note that you can still leave automatic growth to ensure that your tempdb system database does not run out of space.)
- Create as many data files as you have physical cores on your SQL Server 2005 instance. (In other words do not include hyper-threaded (HTT) processors.) This will improve performance by allowing SQL Server 2005 to take advantage of the proportional fill feature of the database engine, and reduce the contention created through concurrency.
- Ensure that all data files are of equal size. This will ensure that SQL Server 2005’s proportional fill feature of the database engine will function optimally.
- Consider using trace flag -T1118 to reduce the contention on your SGAM pages. SQL Server 2005 will no longer use mixed extents for the allocation of database objects.
The real question is how large your initial tempdb system database size should be. Generally, disk space is not a capacity, so remember that you can afford to be generous. A lot of times I allocate a generous amount of space, beyond what the SQL Server 2005 instance really needs, as I know that there is plenty of unutilized space on that partition in any case. So then it is a matter of monitoring your SQL Server 2005 instance to determine whether the space that you have allocated has been sufficient, and adjust accordingly.
Monitoring the tempdb System Database
You should monitor your tempdb system database to help determine if the sizes that you have allocated to the data and log file has been sufficient. Again the rationale is to avoid automatic growth as that will degrade performance.
After monitoring the following performance object counters you should be able to adjust the initial size of your tempdb system database data and log files:
- SQLServer:Transactions : Free Space in tempdb (KB)
- SQL Server:Databases : Log File(s) Used Size (KB)
- SQL Server:Databases : Data File(s) Size (KB)
- SQL Server:Databases : Log File(s) Size (KB)
You can also query the sys.dm_db_file_space_usage DMV, which was added by Microsoft to specifically monitor the space utilization of the various objects in the tempdb system database files.
Victor Isakov
Victor Isakov – Resume (July 2007)
Personal Details
Name
Victor Isakov
Email Address
Brief
Victor Isakov is a Database Architect and Microsoft Certified Trainer who provides consulting and training services to various organizations in the public, private and NGO sectors globally, and been involved in different capacities at various international events and conferences. He has authored a number of books on SQL Server and worked closely with Microsoft to develop the new generation of SQL Server 2005 Certification and Microsoft Official Curriculum for both instructor led training (ILT) and e-Learning.
Industry Details
Education
University of NSW (Sydney, Australia)
- Bachelor Science (Computer Science)
- Bachelor Law
Industry Qualifications
Chauncey Group International:
- Certified Technical Trainer
Microsoft:
- Microsoft Certified Professional
- Microsoft Certified Trainer
- Microsoft Certified Systems Engineer
- Microsoft Certified Database Administrator
- Microsoft Certified Technology Specialist: SQL Server 2005
- Microsoft Certified IT Professional: Database Administrator
- Microsoft Certified IT Professional: Database Developer
- Microsoft Certified Technology Specialist: SQL Server 2005 Business Intelligence
- Microsoft Certified IT Professional: Business Intelligence Developer
- Microsoft SQL Ranger
Books / Publications
- SQL Server™ 7 24seven™; The Essential Resource for System Administrators Sybex INC, Alameda CA, 0-7821-2508-5
- MCITP: Microsoft SQL Server 2005 Database Design Study Guide (70-441) Wiley Publishing INC, San Francisco CA, 0-470-04052-1
- MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance Study Guide (70-444) Wiley Publishing INC, San Francisco CA, 0-470-04052-1
- Microsoft SQL Server 2005 Administrator’s Companion Microsoft Press, Redmond WA, 0-7356-2198-5
Certification / Courseware
Author / Lead Subject Matter Expert:
- Designing a High Availability Database Solution using Microsoft® SQL Server™ 2005 Microsoft Official Curriculum Course 2788A
- Designing Microsoft® SQL Server™ 2005 Databases Microsoft e-Learning Course 2782AE
Designer / Technical Reviewer:
- Designing a High Availability Database Solution using Microsoft® SQL Server™ 2005 Microsoft Official Curriculum Course 2788A
- Writing Queries Using Microsoft® SQL Server™ 2005 Transact-SQL Microsoft e-Learning Course 2778AE
- Configuring SAP on Microsoft® SQL Server™ 2005 Microsoft e-Learning Course 5329AE
- “Series 3” Certification for SQL Server
Alpha Technical Reviewer:
- Designing Database Solutions by Using Microsoft® SQL Server™ 2005 Microsoft Exam 70-441
- Designing a Database Server Infrastructure by Using Microsoft® SQL Server™ 2005 Microsoft Exam 70-443
- Optimizing and Maintaining a Database Administration Solution Using Microsoft® SQL Server™ 2005 Microsoft Exam 70-444
- Microsoft® SQL Server™ 2005 Business Intelligence – Implementation and Maintenance Microsoft Exam 70-445
- Designing a Business Intelligence Solution by Using Microsoft® SQL Server™ 2005 Microsoft Exam 70-446
Beta Technical Reviewer:
- Designing Database Solutions by Using Microsoft® SQL Server™ 2005 Microsoft Exam 70-441
- Microsoft® SQL Server™ Implementation and Maintenance Microsoft Exam 70-431
- Designing a Database Server Infrastructure by Using Microsoft® SQL Server™ 2005 Microsoft Exam 70-443
- Optimizing and Maintaining a Database Administration Solution Using Microsoft® SQL Server™ 2005 Microsoft Exam 70-444
Standard Setting Reviewer:
- Microsoft® SQL Server™ Implementation and Maintenance Microsoft Exam 70-431
Conferences / Seminars
Conference Proctor:
- Microsoft TechEd 2004 (Canberra, Australia)
- Microsoft TechEd 2005 (Orlando, US)
- Microsoft TechEd 2005 (Amsterdam, Netherlands)
- Microsoft TechEd 2005 (Gold Coast, Australia)
- Microsoft Professional Developer Conference (PDC) 2005 (Los Angeles, US)
- SQL Server 2005 Upgrade Technical Bootcamp (Redmond, US)
- Conference Ask-The-Expert (DBA & Developer Tracks):
- Microsoft TechEd 2005 (Amsterdam, Netherlands)
- Microsoft TechEd 2005 (Gold Coast, Australia)
Conference Presenter:
- Understanding Index Usage and Indexing Best Practices in SQL Server 2005 Microsoft TechEd 2005 (Gold Coast, Australia)
- SQL Server 2005 Deep Dive Pre-Conference Workshop Microsoft TechEd 2005 (Gold Coast, Australia)
- Methodology for Upgrading to SQL Server 2005 Microsoft TechEd 2006 (Boston, US), Microsoft TechEd 2006 (Sydney, Australia)
- A Methodology for Troubleshooting and Tuning SQL Server Session SQL Server Magazine Connections 2006 (Las Vegas, US)
- Designing a High Availability Solution for SQL Server 2005 Session SQL Server Magazine Connections 2006 (Las Vegas, US)
- SQL Server 2005 Deep Dive Pre-Conference Workshop Microsoft TechEd 2005 (Gold Coast, Australia)
- SQL Server 2005 Table and Index Partitioning PASS Community Summit 2006 (Seattle, US)
Skill Details
Summary
Since 1994 I have worked as an independent consultant providing complex IT solutions for organizations in the Australasia region. I specialize in:
- Designing and implementing Windows networks in both LAN and WAN environments
- Providing technical support for operating system, infrastructure and database technology
- Designing, testing and implementing an SOE (Standard Operating Environment)
- Deploying and maintaining desktop applications
- Architecting (analyzing needs, designing and implementing) database solutions
- Performance tuning, troubleshooting and optimization of existing database solutions
- Documentation of existing database solutions
- Refactoring existing database solutions
- Extracting, transforming and loading data between heterogeneous database solutions
- Architecting (analyzing needs, designing and implementing) datawarehouse solutions
I have consistently trained all the SQL Server administration, development, performance tuning and datawarehousing courses (based on Microsoft Official Curriculum) across Australasia for over 10 years.
I specialize in providing customized, on-site training for SQL Server internals, performance tuning and best practices.
In 2007 I was invited by Microsoft to attend the SQL Ranger program at Redmond (US). I am the first non-Microsoft attendee to have completed the MCA: Database course.
Technology
- Microsoft Analysis Services 7.0, 2000, 2005, 2008
- Microsoft Operations Management (MOM) 2000, 2005
- Microsoft SQL Server 4.21a, 6.0, 6.5, 7.0, 2000, 2005, 2008
- Microsoft Systems Management Server (SMS) 1.0, 1.1, 1.2, 2.0, 2003
- Microsoft Windows 3.5, 4.0, XP, 2000, 2003, Vista, 2008
- Sybase 10, 11, 12
Clients
Various organizations in the private, public and NGO sectors including:
- BT Financial Group (Sydney, Australia)
- Department of Education & Training (Sydney, Australia)
- Centre for Disease Control and Prevention (CDC) (Lusaka, Zambia)
- Microsoft (Redmond, US)
- Westpac Institutional Bank (Sydney, Australia)
- United Nations (UN) (Dili, East Timor)
- University of NSW (Sydney, Australia)
Victor Isakov 