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)

November 2009 - Posts - SQL Server Knowledge Sharing Network (@sqlmaster)

November 2009 - Posts

How fast you can obtain information about missing indexes? Its easy and quick!

On the subject line the process to find the answer is not a short one!

By design SQL Server engine takes care when the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature from 2008 version (in specific) enables you to access information about these indexes so you can decide whether they should be implemented. There are no special tools involved here and the missing index feature uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance. ...read more..!

 

SQL Server Manageability series - shortcut for SSMS keyboard shortcuts!

For any user manage the work with short-cut is desirable, for better productivity in your work (with UI tools) I always suggest to manage it by keyboard not with the mouse.

Here is another block of SQL manageability series I want to cover based on feedback that I have received for earlier posts on this blog, this time let us visit on keyboard shortcuts.

Since the version 2005 release Microsoft has done good job for SQL Server Management Studio offering the users 2 kinds of keyboard schemes. One of the is a standard scheme based on Visual Studio which is used by default and another one is SQL Server 2000 scheme which goes by its name offering in particular the keyboard shortcuts from the Query Analyzer. I have had issues to use the keyboard shortcuts in Query Editor due to multiple versions of SQL Server installed on my machine (from 2000 to 2005 to 2008 tools & Express editions). So in order to workaround the issue and as per BOL indication you have to change the keyboard scheme or add additional keyboard shortcuts as on the Tools menu, click Options. Select the desired keyboard scheme on the Environment, Keyboard page.

On top of that here is my collection of SSMS keyboard shortcuts, which is documented clearly in BOL too:

For generic & BIDS related work:

Menu Activation Keyboard Shortcuts

Action Standard SQL Server 2000

Move to the SQL Server Management Studio menu bar

ALT

ALT

Activate the menu for a tool component

ALT+HYPHEN

ALT+HYPHEN

Display the context menu

SHIFT+F10

SHIFT+F10

Display the New File dialog box to create a file

CTRL+N

No equivalent

Display the New Project dialog box to create a new project

CTRL+SHIFT+N

CTRL+SHIFT+N

Display the Open File dialog box to open an existing file

CTRL+O

CTRL+SHIFT+INS

Display the Open Project dialog box to open an existing project

CTRL+SHIFT+O

No equivalent

Display the Add New Item dialog box to add a new file to the current project

CTRL+SHIFT+A

No equivalent

Display the Add Existing Item dialog box to add an existing file to the current project

ALT+SHIFT+A

No equivalent

Display the Query Designer

CTRL+SHIFT+Q

CTRL+SHIFT+Q

Close a menu or dialog box, canceling the action

ESC

ESC

 Manage toolbard keyboard shortcuts

Action

Standard SQL Server 2000

Close the current MDI child window

CTRL+F4

CTRL+F4

Print

CTRL+P

CTRL+P

Exit

ALT+F4

ALT+F4

Toggle full screen mode

SHIFT+ALT+ENTER

SHIFT+ALT+ENTER

Close the current tool window

SHIFT+ESC

SHIFT+ESC

Cycle through the next MDI child windows

CTRL+TAB

CTRL+TAB

Cycle through the previous MDI child windows

CTRL+SHIFT+TAB

CTRL+SHIFT+TAB

Move to the current tool window toolbar

SHIFT+ALT

No equivalent

Move to the next tool window

ALT+F6

ALT+F6

Move to the previously selected window

SHIFT+ALT+F6

SHIFT+ALT+F6

Opens a new query editor window

No equivalent

CTRL+O

Display Object Explorer

F8

F8

Display Registered Servers

CTRL+ALT+G

No equivalent

Display Template Explorer

CTRL+ALT+T

CTRL+ALT+T

Display Solution Explorer

CTRL+ALT+L

CTRL+ALT+L

Display the Summary Window

F7

F7

Display the Properties Window

F4

F4

Display the Toolbox

CTRL+ALT+X

CTRL+ALT+X

Display the Bookmarks Window

CTRL+K, CTRL+W

No equivalent

Display the Browser Window

CTRL+ALT+R

CTRL+ALT+R

Display the Error List Window (Transact-SQL Editor only)

CRTL+\, CTRL+E

CRTL+\, CTRL+E

TSQL debugger shortcuts for programmers:

Action Standard SQL Server 2000

Start or continue debugging

ALT+F5

No equivalent

Stop debugging

SHIFT+F5

No equivalent

Step into

F11

No equivalent

Step over

F10

No equivalent

Step out

SHIFT+F11

No equivalent

Implement the Run To Cursor command

CTRL+F10

No equivalent

Display the QuickWatch dialog box

CTRL+ALT+Q

No equivalent

Toggle breakpoint

F9

No equivalent

Delete all breakpoints

CTRL+SHIFT+F9

No equivalent

Display the Breakpoints window

CTRL+ALT+B

No equivalent

Break all

CTRL+ALT+BREAK

No equivalent

Display the Watch 1 window

CTRL+ALT+W, 1

No equivalent

Display the Watch 2 window

CTRL+ALT+W, 2

No equivalent

Display the Watch 3 window

CTRL+ALT+W, 3

No equivalent

Display the Watch 4 window

CTRL+ALT+W, 4

No equivalent

Display the Autos window

CTRL+ALT+V, A

No equivalent

Display the Locals window

CTRL+ALT+V, L

No equivalent

Display the Immediate window

CTRL+ALT+I

No equivalent

Display the Call Stack window

CTRL+ALT+C

No equivalent

Display the Threads window

CTRL+ALT+H

No equivalent

.... download and update SQL Server Books Online [2005 & 2008] and visit shortcuts [SQL Server Management Studio] section for more shortcuts.

My frequently used keyboard shortcuts in SSMS:

ALT+F+E - to connect to any Server instance (DB engine/SSIS/SSRS/SSAS)

F8 - Display Object Explorer and F7 - to obtain details of each different node in the Object Explorer tree

CTRL+ALT+G - to show SQL Server Local Server groups and Central Management Server groups. FYI, local Server Groups are related SQL Server systems that SSMS connects to. Central Management Server groups store groups of SQL Server systems that let you perform management tasks to be enacted on all servers in a group.

CTRL+ALT+T - display the template explorer

CTRL+L - display execution plan

 

There are many more available and fellow MVP Pinal Dave's contribution to the community on the subject: Download SQL Server Management Studio Keyboard Shortcuts  link.

 

SQL Server 2008 - Unstructured Data Storage Solutions and Best practices, SQLBits

 

Data is most business-critical for any organization and no doubt that they invest heavily to make data management system more efficient, above all the storage is important piece in the puzzle.

Now a days there are no limitation in terms of what kind or type of data you can store in the database, where SQL Server has no such limitation since version 2008. Storage of relational and beyond relational data is managable but think when such data (unstructured) needs to be managed on high volume in the form of digital images, documents, videos, and other multimedia formats—and these new data formats are quickly becoming a key component in formal and informal business processes that integrate with existing business applications, comply with regulatory requirements, or simply provide a richer user experience.

For instance consider the business requirement of an Insurance company to store policy documents for retrieval during the claims processing and an online retailer needs to store the images & video footage of the products that are sold on the website.

 

On 21st November I'm speaking on the subject the solutions of Unstructured Data Storage using SQL Server 2008 with a tour of best practices. I have designed the session that can  help you to build a strategy and learn how the SQL Server 2008 new feature is addressing the needs of storing large volumes of unstructured data, while still retaining relational control over it. These solutions enable you to reduce both capital and operational costs for large stores of unstructured data. In this session we also cover the best practices in such storage kind of projects.

 

The examples that are give above are sample, come to the session to know more on strategy you can follow by the way in which unstructured digital data is used in businesses throughout the world; and as it becomes increasingly easy to create digital content, organizations are finding new, innovative ways to use this digital content to improve or extend their business capabilities.

During the Microsoft Tech-Ed North America conference I had been to Kevin Farlee's session to know more on the subject, I will cover few from my learnings & experience on the topic!

In this regard on the topic agenda I'm speaking in a major SQL Server User conference in UK on 20th November 2009 - SQLBits V register here if you haven't done yet!

 

 

Achieve SQL Server 2008 High Availability and Disaster Recovery - technical case study, SQLBits

How can you achieve the High Availability & Disaster Recovery  on database platform?

What are factors involved to keep the system available to maintain revenue stream?

What kind of management principles you need to implement?

Well there are lot more questions involved to the subject, only few important that I have listed above and in this regard on the topic agenda I'm speaking in a major SQL Server User conference in UK on 20th November 2009 - SQLBits V register here if you haven't done yet!

There is an in-depth on the subject to achieve maximum throughput for data, and also you should eliminate all single points of failure in architecture, and developed procedures for patching servers, upgrading software, and implementing application changes that preserve high availability. Based on these efforts, you can achieve 99.99 percent uptime, including both planned and unplanned downtime.

The session will highlight the important decisions and procedures that are employed to maintain maximum availability with minimal downtime. We will also cover the design of a 'achieveable' high-availability strategy by understanding all the requirements the strategy needs to meet. Lets not forget the limitations of technology and little compromise on evaluation of factors within platform, application and also consider the list of requirements that must be used when choosing which technologies to use.

No doubt that SQL Server 2008 provides all the technologies needed to implement a high-availability strategy, but knowing you have implemented a successful high-availability strategy relies on testing the system against failures—it is far better to simulate a failure with all staff on hand to aid with recovery than to experience a failure when no one expects it and end up with more downtime and data loss than necessary.

This information will be of interest to senior IT managers, project leads, architects, and database administrators (DBAs). All the content is presented from my own experience and industry best practices with a tour of Microsoft methods, it will sum up a great deal of procedures to achieve high availability and disaster recovery.

See you there!

 

How do I monitor problems with replication?

When you setup a program or application then you have equal responsibility to monitor the process/problems too.

Similar to this monitoring a replication topology is an important aspect of deploying SQL Server replication. As the process is distributed and it is essential to track activity and status across all computers involved in replication. We have bunch of tools that can help the user/DBA/Developer to monitor replication, they are:

  • Replication Monitor: most important tool for monitoring replication, presenting a Publisher-focused view of all replication activity. For more information, see Monitoring Replication with Replication Monitor.

  • SQL Server Management Studio:provides access to Replication Monitor. It also allows you to view the current status and last message logged by the following agents and allows you start and stop each agent: Log Reader Agent, Snapshot Agent, Merge Agent, and Distribution Agent.

  • TSQL and RMO: both interfaces allow you to monitor all types of replication from the Distributor. Merge replication also provides the ability to monitor replication from the Subscriber.

  • Alerts for replication agent events: provides a number of predefined alerts for replication agent events, and you can create additional alerts if necessary. Alerts can be used to trigger an automated response to an event and/or notify an administrator. For more information, see Using Alerts for Replication Agent Events.

  • System Monitor: a best tool for monitoring performance, providing a number of counters for replication. For more information, see Monitoring Replication with System Monitor.
If you observe all these aspects are documented in BOL thoroughly with a code example too. In general within any organisation having a good list of tools.
Also it is a good practice to send replication events (like any other events of interest) to the eventlogs and then use tools that monitor
the required logs to send notification. FOr a better consistency of alerting and notification you must test them thoroughly.
From BOL:

To monitor Publishers, publications, and subscriptions from the Distributor

  1. At the Distributor on the distribution database, execute sp_replmonitorhelppublisher. This returns monitoring information for all Publishers using this Distributor. To limit the result set to a single Publisher, specify @publisher.

  2. At the Distributor on the distribution database, execute sp_replmonitorhelppublication. This returns monitoring information for all publications using this Distributor. To limit the result set to a single Publisher, publication, or published database, specify @publisher, @publication, or @publisher_db, respectively.

  3. At the Distributor on the distribution database, execute sp_replmonitorhelpsubscription. This returns monitoring information for all subscriptions using this Distributor. To limit the result set to subscriptions belonging to a single Publisher, publication, or published database, specify @publisher, @publication, or @publisher_db, respectively.

To monitor transactional commands waiting to be applied at the Subscriber

  1. At the Distributor on the distribution database, execute sp_replmonitorsubscriptionpendingcmds. This returns monitoring information for all commands pending for all subscriptions using this Distributor. To limit the result set to commands pending for subscriptions belonging to a single Publisher, Subscriber, publication, or published database, specify @publisher, @subscriber, @publication, or @publisher_db, respectively.

 

To monitor merge changes waiting to be uploaded or downloaded

  1. At the Publisher on the publication database, execute sp_showpendingchanges. This returns a result set showing information on changes that are waiting to be replicated to Subscribers. To limit the result set to changes that belong to a single publication or article, specify @publication or @article, respectively.

  2. At a Subscriber on the subscription database, execute sp_showpendingchanges. This returns a result set showing information on changes that are waiting to be replicated to the Publisher. To limit the result set to changes that belong to a single publication or article, specify @publication or @article, respectively.

To view and modify the monitor threshold metrics for a publication

  1. At the Distributor on the distribution database, execute sp_replmonitorhelppublicationthresholds. This returns the monitoring thresholds set for all publications using this Distributor. To limit the result set to monitor thresholds to publications belonging to a single Publisher or published database or to a single publication, specify @publisher, @publisher_db, or @publication, respectively. Note the value of Metric_id for any thresholds that must be changed. For more information, see Setting Thresholds and Warnings in Replication Monitor.

  2. At the Distributor on the distribution database, execute sp_replmonitorchangepublicationthreshold. Specify the following as needed:

    • The Metric_id value obtained in step 1 for @metric_id.

    • A new value for the monitor threshold metric for @value.

    • A value of 1 for @shouldalert for an alert to be logged when this threshold is reached, or a value of 0 if an alert is not needed.

    • A value of 1 for @mode to enable the monitor threshold metric or a value of 2 to disable it.
The problem is many users who ask the questions doesn't know how to get information out of BOL, so it is better to do this one time job of download the latest version and search for information.
More to come.......

SQL Server Replication upgrade practices - choose optimal upgrade option

SQL Server Upgrade task needs a thorough planning and testing before you deploy the process in production environment. In this regard when performing an upgrade to a replicated environment, DBAs must make sure that all changes have been applied to all the databases that are involved in the replication topology before they attempt the upgrade.... Read More... 

 

How SQL Server 2008 can help to build fast track data warehouse?

Beginning of this year I have posted here about Build your BI skills in fast track data warehouse  and based on that I have had few questions and feedback on how best we can take help of SQL Server 2008 features to build the fast track data warehouse.

Based on the material and resources available on Microsoft site the approach must be on data access layer, configuration (hardware, software) and maintaining the normalization practices.When you talk about Business Intelligence then data access layer is key to work on consecutive I/O access for data warehouse workloads.The platform must be scalable in terms of performance & access methods on hardware side in addition to data layer side. By design the data warehouse store will have large file reads with less writes, this involves one-block operation on disk level to obtain the data having a penalty (minimum) of latency with the disk drive head having to move to a new location. Whereas the hardware configuration of Random I/O, on the other hand, involves large numbers of seeks and rotations, and is usually much slower.

As an Analyst for BI implementation you need to understand the performance constraints related to configuration techniques on hardware, software & application (if needed) to maximize the sequential I/O data access methods. By design the I/O from an instance of the SQL Server Database Engine includes logical and physical reads, such logical read occurs every time the DB Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache.You can help the DB engine optimizer in a way that relational engine determines the most effective access method (such as a table scan, an index scan, or a keyed read); the access methods and buffer manager components of the storage engine determine the general pattern of reads to perform, and optimize the reads required to implement the access method.

Such performance optimization methods are called as read-ahead, BOL clarifies that "....the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk". This mechanism helps the DB engine to manage upto 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. This is where you will observe table scans and index scans, this is entirely a different topic that will be covered on performance tuning blog section on this site.

Coming back to reference of configuration model (Fast Track Data Warehouse) approach from Microsoft, it is key that maintaining performance over time is preserving the sequential order of the data on disk by preventing and/or minimizing the fragmentation of the data under normal operating conditions. The programme is highlighted with an objectives such as:

  • Providing an initial SSDW sequential I/O data access experience with known performance and scalability characteristics.
  • Developing a method that is focused on understanding the performance characteristics, limitations, and the cause-and-effect relationships of each component contained in the configuration.
  • Providing proven recommendations of SSDW best practices for sequential I/O data access that are simple, easy to implement, and include holistic recommendations for database setup, configuration, and data loading.

One of the big question was How the fast track approach is different?

Microsoft documentation highlights that approach is specifically focused on building scalable CPU core-balanced configurations to support SSDW sequential I/O data access workloads. A holistic and vendor-agnostic approach is used to address the CPU to I/O balance of the system to efficiently design a configuration that is intentionally targeted at and optimized for sequential data access. This is designed to achieve the best possible performance by matching all internal components to their individual maximum performance level while not exceeding the capacity of any components above it in the stack, and then applying principles in physical and logical layout that allow the hardware to provide this maximum performance in a real environment.

So you need to be aware that to attain such a scalable platform you need to determine the right mix of hardware, software, layout, and configuration settings differs significantly from conventional reference architectures because the focus is on determining the right file and software configuration while maintaining maximum performance of all items in the hardware stack, starting with the CPU and the cores, and working back down to the storage.

The underlying the optimization technique includes usage of building-block approach, to keep performance constant per MB of data processed, there must be enough complete building blocks used to scale up the CPU and scale out storage together. Scaling up the CPU cores in unison with storage components provides near constant performance for larger data volumes. It is also possible to increase storage capacity without adding more CPU cores by using larger drives or more drives per LUN; however this implies that queries covering a wider range of data will have lower performance. Important to note is that adding more data but still querying the same sized ranges per query will not decrease performance.

 Lastly you can download Spreadsheet Calculator that has been developed to assist with the optimization of the software configuration on the selected hardware based upon specified component performance ratings and internal data feed requirements for SQL Server.

  • The spreadsheet helps to advise the number of storage arrays, disks, and LUN’s required to achieve the specified throughput given the hardware detailed in the calculator.
  • The spreadsheet has pre-populated ratings determined from component testing and research for both the EMC CX4-240 and HP MSA2000 storage options. See above for details on how to determine the SQL Server per-core requirement for any new compute node server configuration.
  • New configurations and new hardware can be added to the calculator by cloning an existing EMC or HP sheet, renaming it to the new configuration, and entering the specific factors for the hardware being reviewed. The new sheet will then utilize the new factors when doing its calculations for LUN’s, drives, and the number of required storage arrays.

Review the Implementing a SQL Server Fast Track Data Warehouse An Introduction to Fast Track Data Warehouse Architectures links too.