Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

February 2008 - Posts - SQL Server Knowledge Sharing Network (@sqlmaster)

February 2008 - Posts

Microsoft Empower Program - good deal for ISVs

Microsoft has rolled out new program called - Empower Program that will enable the ISVs to receive software, support and additional resources designed to help you lower your development costs, test your software and speed your solution's time-to-market.

Jason from MSFT/SSP quoted about this program as follows: 



Empower Benefits
  • Up to five internal-use software licenses:
    • Windows Vista or Windows XP Professional
    • Microsoft Office 2007 or 2003
    • 2007 Microsoft Office system or Microsoft Office 2003
  • One (1) software license and up to five (5) Client Access Licenses (CALs) for:
    • Windows Server 2003 (Standard or Enterprise Edition)
    • Microsoft Exchange Server 2003 (Standard or Enterprise Edition)
    • Microsoft SQL Server 2005 (Standard or Enterprise Edition)
    • Microsoft Office SharePoint Server 2007 or 2003
  • Development and testing license: One (1) MSDN Premium Subscription Media Kit and five (5) user licenses**
    • One MSDN Premium Subscription Media Kit (DVD). Each MSDN Premium Subscription includes the full range of Microsoft Servers integrated server software, all Microsoft operating systems, Microsoft productivity products (Microsoft Office Developer Edition, Microsoft Project, FrontPage, Visio, and MapPoint***), as well as access to software developer kits, driver development kits, and the MSDN Library. Each subscription also gives you priority access to new product releases, updates, and betas through the MSDN Subscriber Downloads site.
    • Five (5) licenses** for Microsoft Visual Studio 2005 Professional Edition. Up to five (5) users, concurrent with development and testing licenses provided with the MSDN Premium Subscription, may use Visual Studio 2005 Professional Edition.
  • MSDN online managed support newsgroups (English only)
    • Forums for your software, hardware, and related technology questions supported by Microsoft support professionals
  • ISV Advisory Service (10 hours)
    • This new phone-based consultation service helps you develop products using Microsoft technologies. Select from a broad range of services, including ad hoc development advice and best-practice recommendations.

For full details see: http://partner.microsoft.com/empower


Receive 24 hours of free, online training on new Microsoft products

Jump into training with Microsoft Learning!

This was the theme with Heroes Happen Here launch and here are further details from Microsoft Learning page:

jump in now

Microsoft Certified Trainers present 24 hours of live, online training on four rotating topics—with one new topic every 15 minutes. Topics include:

Join the Microsoft Office Live Meeting on February 27–28, 2008.

First Look Clinic

Sign up for a two-hour online clinic: Getting Started with Server Management in Windows Server 2008. Register now—this free offer is valid for the month of March only!

Few hours to go for Windows Server, Visual Studio and SQL Server 2008 launch, here is big deal on SQL release date!

I have been following Dan Jones's blog on " Launch_T-1Launch_T-3 & Launch_T-5" series about SQL Server Launch in US (PST), as a Development Program Manager they have huge task ahead to complete and its not that easy to manage as per the schedule.

Also you may be aware about 'postponement' of SQL Server  2008 version release (RTM) and I should say Dan has given much of explanation on their reasons & revisions on this BigDeal blog post, these products next version launch is not a small meeting to finish!

Vote now: Index defragmentation best practices for SQL Server 2005 & 2008 documentation

From the snippet of Tibor Karaszi, SQL Server MVP I have raised a feedback form on Connect.Microsoft website to get newer version (SQL Server 2005) about Index Defragmentation Best practices information.

Vote your interest to get newer documentation quickly for most of our benefit to attain the performance.

Though the following feedback received on Jan 04 2008 -  

Thanks for your feedback. We are looking into what white papers to write for Katmai. We will consider this as one of the areas

Posted by Microsoft on 04/12/2007 at 17:04
It is better to get this out quickly for more information.

SQL Server 2005 Enterprise Edition - make data available while creating indexes on large tables!

Though it is not a best practice to perform a CREATE INDEX on large tables during the online hours where you have number of users accessing the metadata of SQL Server database.

Within SQL Server 2005 Enterprise Edition you can perform ONLINE indexes operation, in this regard review the blogs by SQLQuery Processing team about offline & online index operation. Still you cannot perform ONLINE index operations that are four kind of indexes with the exceptions such as:

  • Disabled indexes
  • XML indexes
  • Indexes on local temp tables
  • Partitioned indexes
  • Clustered indexes if the underlying table contains LOB(image/text) data types
  • Nonclustered indexes that are defined with LOB (image/text) data type columns

So as per the subject link say if you need to access the data while creating index on a large table to minimize the down time, here is the trick. By default SQL engine puts up schema modification lock (SCH-M) on that table that prevents all the access (SELECT, UPDATE, DELETE) to the data until it finishes the CREATE INDEX operations. This is bit different to the action when you try to create NON CLUSTERED index which puts up shared lock (S) that will also prevent any data modifications only but still it is available for data readability.

Similarly when using online index operations on tables, SQL Server will still put an SCH-M lock for a clustered index or Shared (S) lock for a non-clustered index on the underlying table, but only for a very short period of time that is nothing but during the start and end phase of the index operation. So when you need to create index on a large table within online operations better to perform the ONLINE operation by using:

ON SQLTips (tip) with (ONLINE=ON)


Bear in mind this is only available in Enterprise Edition of SQL Server and not in other editions, by default you should perform such index operations during less traffic hours on the database for the better access for querying and updating of the underlying table during the index creation process.

(cross relation from my blog, with update)

SQL Server licensing with having multi-core CPU technology, confusing?

In addition to hyper-threading (i.e. multiple threads execute on a single physical CPU appearing as 2 logical CPUs) and dual-core technology (i.e. a single CPU socket that has more than 1 core appearing as multiple logical CPUs), chip vendors are beginning to release processors with more than 2 cores.  I am considering purchasing a new SQL Server with multi-core processors to support a SQL Server 2005 Standard Edition deployment.  What are the impacts when using a 4-core processor with SQL Server 2005 Standard Edition?  Will I only be able to make use of a single physical CPU since Standard Edition is limited to 4 CPUs? Additionally, what is the overall licensing policy in relation to multi-core technologies with SQL Server 2005?

One key competitive licensing advantage of SQL Server is related to this exact question - for the purposes of both licensing and CPU edition support, SQL Server considers the number of physical sockets/CPUs, regardless of the number of cores on the processor.  So, for example, if you are planning to deploy
SQL Server 2005 Standard Edition, which supports up to 4 CPUs, that means Standard Edition will support 4 physical CPU sockets, regardless of the number of cores in each CPU.  If you have 4 physical CPUs with 4 cores each, then your SQL Server Standard Edition deployment would have 16 logical CPUs to make use of. Additionally, even though you have 16 cores/logical CPUs, the licensing of this requires that you pay for only the 4 physical CPUs, not all 16 cores.  This offers a very attractive return on investment (ROI) for any organization looking at the latest hardware with SQL Server 2005. 

For additional information on SQL Server 2005 and multi-core licensing, reference the SQL Server Multicore Licensing Policy article.

Next Steps

  • With these new CPU options, research these options with your hardware vendor of choice to see what opportunities are available with their latest and greatest servers
  • If your applications are CPU bound and you are unable to tune the SQL Server and queries any further, then research the multi-core opportunities to meet your CPU needs
  • Keep in mind the huge pricing advantage for SQL Server 2005 with support for multiple cores when you start to look at other DBMS's in your environment or considering upgrading
Cross post from SSQA.net
Posted by ssqa.net | 1 comment(s)

Is there a way of recylce the SQLAgent.out file in SQL Server 2000 and SQL Server 2005?

You may be aware about SP_CYCLE_ERRORLOG from older SQL Server versions, this will only take care of SQL Server error log and not with SQLAgent error log.

So the question is there any way out to recycle the SQLAgent log file too without restarting this service?

Yes, it is possible and only in SQL Server 2005. As we have new statement called sp_cycle_agent_errorlog that will take care of these issues. But consider the fact that unless there is a requirement or huge log file for SQLAGENT.OUT you need not to perform the recycle of agent log file, but in any case if you have included any execution trace messages then the log file will outgrow within a day or two that is dependant on the SQL Server usage. This advanced setting within SQLAgent is not required unless you have to troubleshoot any particular process that is appearing intermittently within the system.

Take this on SQL Server 2000 version where the agent cycle process is not available, in this case only way is to restart SQLAgent service. This may not have outage to the application but if you have any frequent jobs such as running every minute then you may have a bit problem in running the job unless the service is back online. Also you should think that do you have such a regular jobs running even without a gap of 5 minutes to stop & start SQLAgent on these instances?


SQL Server 2005 Index Optimization Best Practices

Do you have a maintenance window to perform database reindexing?

Have you ever performed full set of re-organize and re-index process on a bigger database?

Do you know there are best practices to deploy in this regard?

On large database systems, with large numbers of insert and update commands, the problem of index fragmentation is one of the main causes of performance degradation and a proper index optimization strategy is a must. Also following are set of counters you need to keep in mind:

  • Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on column which are used in JOIN Condition.
  • Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of all the insert, updates, deletes to the table.
  • Smaller Index Key gives better performance than Index key which covers large data or many columns
  • Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
  • Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.

Also there are few basic guidelines when you need to create a database from scratch such as,

  • Design a normalized database.
  • Optimize a database design by denormalizing.
  • Optimize data storage.
  • Manage concurrency - by selecting the appropriate transaction isolation level.
  • Select a locking granularity level.
  • Optimize and tune queries for performance.
  • Optimize an indexing strategy.
  • Decide when cursors are appropriate.
  • Identify and resolve performance-limiting problems.
  • Be familiar with index structures and index utilization. Specifically, they must understand the interaction between non-clustered indexes, clustered indexes and heaps. A must know why a covering index can improve performance.
  • Be able to design a database to third normal form (3NF) and know the trade offs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas.

Above all a DBA must have strong monitoring and troubleshooting skills, including using monitoring tools such as SYSMON & PROFILER. TO just repeat the content from my own blog about "SQL Server 2005 & 2000 - Index optimization best practices" that:

In SQL Server 2000 for index defragmentation DBCC DBREINDEX and DBCC INDEXDEFRAG statements  are used, as you are aware defragmentation on table does not yield performance gains in every case. Every scenario is different. Similarly, determining when you should run the defragmentation statements requires analysis. Run DBCC SHOWCONTIG to determine index fragmentation and the decision of whether to use DBCC DBREINDEX or DBCC INDEXDEFRAG is based on your availability needs and the hardware environment. Few times Updating statistics is useful but it will be a side effect of DBCC DBREINDEX, which is not the case with DBCC INDEXDEFRAG. You can increase the effectiveness of DBCC INDEXDEFRAG by running UPDATE STATISTICS. Refer to this KBA to troubleshoot any slow performance on the application and defragment best practices white paper.

Make use of SORT_IN_TEMPDB option in SQL 2005 that uses temporary storage for sorting and other intermediate tasks while creating or rebuilding an index. The storage refers to be in TEMPDB and few times it can be used from the user database, or it can be used from the TEMPDB database. Using this feature determines where the intermediate sort results, generated during index creation, are stored. You can take help of sys.indexes catalog view and sys.stats catalog views to examine the curren value and settings on those indexes.  When ON, the sort results are stored in TEMPDB and when OFF, the sort results are stored in the filegroup or partition scheme in which the resulting index is stored.  Not all index options values are stored in metadata. Those values that are stored in metadata can be viewed in the appropriate catalog views. Refer to this TEMPDB whitepaper on the enhancement and usage of this feature in SQL Server 2005.

Here is the list that makes few best practices to follow for TEMPDB:

    • Place the TEMPDB is on a disk subsystem that provides sufficient I/O throughput, and that the TEMPDB is big enough to accommodate the temporary space that is required for the index create or rebuild operation. It is a best practice to move the TEMPDB to a storage area with sufficient space and performance after you install SQL Server. Also, note that the TEMPDB database is a common resource for the entire instance of SQL Server. You should consider the activities in all the user databases that might be using TEMPDB while you plan for the TEMPDB.

Further read-on in this regard can be followed on this SQLServerINdexOptimization-BestPractices blog post.


More Posts Next page »