SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |
   
#347<<  #348  >>#349

СОДЕРЖАНИЕ

1.СТАТЬИ
1.1.Основы ввода-вывода Microsoft SQL Server. Часть 2 (продолжение)
2.ССЫЛКИ НА СТАТЬИ
2.1.Статьи на русском языке
2.2.Англоязычные статьи
3.ФОРУМ SQL.RU
3.1.Самые популярные темы недели
3.2.Вопросы остались без ответа
4.КНИГИ
4.1.Microsoft SQL Server 2005
 

 

СТАТЬИ

Основы ввода-вывода Microsoft SQL Server. Часть 2 (продолжение)

По материалам статьи Bob Dorr: Microsoft SQL Server I/O Basics. Chapter 2

Защита от чтения устаревших данных (Stale Read)

Чтение устаревших данных стало одной из наиболее часто разбираемых проблем службой поддержки Microsoft SQL Server. Чтением устаревших данных называется такой случай, когда операция физического чтения возвращает старые даны страницы, которые считаются непротиворечивыми на данный момент времени и успешно проходят проверку TORN или CHECKSUM. Вместо текущих данных, операция чтения возвращает предыдущий образ страницы данных. Также, такой случай называют потерянной записью (lost write), потому что самые последние данные, записанные на долговременный носитель, не возвращаются следующей операцией чтения.
Наиболее распространённым источником проблем, проявляющихся в виде чтения устаревших данных и потерянной записи, являются такие компоненты, как упреждающее чтение аппаратного кэша, который может ошибочно возвращать старые, находящиеся в кэше данные, вместо того, что бы вернуть последнюю записанную информацию.
Такие ошибки указывают на серьезные проблемы с подсистемой ввода-вывода, приводящие к искажениям связей страниц, к искажениям при распределении страниц, к потере логических или физических страниц данных, к аварийным отказам при регенерации или восстановлении копий журнала, а также к другим проблемам с целостностью и стабильностью данных.
В основанную на SQL Server 2000 SP3 заплатку (8.00.0847) была добавлена функциональность по обнаружению чтения устаревших данных. Защита от чтения устаревших данных подробно описана в статье Microsoft Knowledge Base: PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems.

Дополнительная функциональность

В SQL Server 2000 SP4 и SQL Server 2005 были внесены изменения, заменившие кольцевой буфер на хеш-таблицы, с помощью которых низкоуровневая проверка чтения устаревших данных выполняется более эффективно. До этого, SQL Server 2000 SP3 проверял чтение устаревших данных только при выявлении других ошибок (605, 823 и т.д.). Используемый в последних версиях дизайн хеш-таблицы использует ряд не ресурсоёмких, логических проверок при чтении страниц, включая проверку чтения устаревших данных любой страницы при включённом флаге трассировки -T818.
В SQL Server 2005 при каждом сохранении страницы на диске в хеш-таблицу вставляется или изменяется запись, содержащая DBID, PAGEID, RECOVERY UNIT и сбрасываемый на долговременный носитель LSN. После завершения операции чтения, в хеш-таблице находятся записи с соответствующими DBID и PAGEID. Значение LSN из хеш-таблицы сверяется со значением LSN из заголовка страницы. Если эти значения LSN не соответствуют друг - другу, страница считается повреждённой. Т.е. страница считается повреждённой, если при последующей операции чтения не будет возвращён самый последний записанный LSN.
Для поддержки высокой производительности операций ввода-вывода и для ограничения используемого объёма памяти, размер хеш-таблицы ограничен. Она содержит записи только о последних записанных страницах данных. Число отслеживаемых операций ввода-вывода отличается в 32-х и 64-х битных версиях SQL Server 2000 SP4 и SQL Server 2005. Для повышения быстродействия, каждая область хэша и связанные с ней записи устроены таким образом, чтобы они представляли собой одну строку в кэше процессора, что призвано ограничить длину цепочки хеширования пятью записями на каждую области памяти. В 32-разрядных версиях общий размер хеш-таблицы ограничен 64КБ (состоящий из 2560 полных записей => 20MБ окно данных), а в 64-битной версии это будет уже 1 МБ (состоящий из 40960 полных записей => 320MБ окно данных).
Выбор такого ограничения размера основывался на результатах тестирования известных ситуаций, при которых возникали чтения устаревших данных или потеря записи. Эти ошибки обычно характеризовались использованием аппаратного кэша памяти, в котором оказывались старые версии страниц данных, и операциями чтения, которые следовали сразу же за операцией записи или с ней пересеклись.

Обнаружение задержек ввода-вывода (Stalled I/O)

С точки зрения производительности, на работу ядра базы данных наиболее ощутимо влияет подсистема ввода-вывода. Остановки или задержки в работе подсистемы ввода-вывода могут привести к снижению параллелизма работающих с SQL Server приложений. Служба поддержки Microsoft SQL Server провела ряд испытаний, направленных на определение того, как увеличение задержек в работе подсистемы ввода-вывода, а также остановки в её работе, влияют на снижение возможностей и на работу SQL Server в целом.
Для версии SQL Server 2000, остановки или задержки ввода-вывода можно обнаружить путём анализа sysprocesses, в которой можно увидеть находящиеся в состоянии ожидания ввода-вывода страниц данных буфера и/или блоков журнала. Принимая во внимание то, что небольшие задержки могут быть отражением состояний ожидания для некоторых фильтр-драйверов или аппаратных компонент; если наблюдается состояние ожидания по своей продолжительности превышающее 30 секунд, это говорит, скорее всего, о наличии серьезных проблем в работе использующего SQL Server приложения.
Начиная с SQL Server 2000 SP4 и SQL Server 2005, используются специальные средства мониторинга и поиска состояний остановки или задержки ввода-вывода, которые по своей продолжительности превышают 15 секунд, и затрагивают операции со страницами данных и журналом транзакций. Работа этих средств в SQL Server 2000 SP4 описана в статье Microsoft Knowledge Base: SQL Server 2000 SP4 diagnostics help detect stalled and stuck I/O operations.
Кроме того, в SQL Server 2000 SP4 и SQL Server 2005 выдаётся больше информации о кратких блокировках. Краткие блокировки используются для гарантии физической стабильности страниц данных во время исполнения операций чтение или записи на долговременный носитель. Эта дополнительная информация о кратких блокировках часто становиться причиной того, что после установки SQL Server 2000 SP4 многим кажется, что SPID блокирует сам себя. То, как информация о кратких блокировках из sysprocesses может использоваться для поиска остановок и задержек ввода-вывода, а так же то, почему SPID может казаться блокирующим сам себя, описывается в статье: The blocked column in the sysprocesses table is populated for latch waits after you install SQL Server 2000 SP4.
SQL Server 2005 выполняет контроль и обнаружение задержек ввода-вывода. Предупреждения о задержках ввода-вывода активируются, когда задержка превышает 15 секунд или больше. В дополнение к этому, были улучшены сообщения об ошибках из-за превышения допустимого времени ожидания краткой блокировки, чтобы они могли явно указать на то, что буфер находится во вводе-выводе. Это является показателем того, что ввод-вывод был остановлен на 300 секунд (пять минут) или дольше.
Существует чёткая разница между выдачей сообщения и регистрацией события. Сообщения появляются только в пятиминутных (или больше) интервалах, когда с файлом происходит новая операция ввода-вывода. Любой посылающий ввод-вывод исполнитель проверяет соответствующий файл и при необходимости выдаёт сообщения. Если были зарегистрированы задержки ввода-вывода, и после последнего сообщения прошли пять минут, в файл регистрации ошибок SQL Server помещается новое сообщение.
Регистрация же осуществляется подпрограммами завершения ввода-вывода и процессом отложенной записи, которые анализируют все ожидания ввода-вывода, фиксируя те из них, которые могут считаться задержками или остановкой ввода-вывода. Регистрация осуществляется во время того, когда запрос ввода-вывода находится на стадии рассмотрения (FALSE == HasOverlappedIoCompleted) и прошло уже 15 или более секунд.
Обратите внимание, что если запрос HasOverlappedIoCompleted возвращает значение FALSE, это указывает на то, что операционная система или подсистема ввода-вывода не завершили запрос ввода-вывода.

ПРОДОЛЖЕНИЕ СЛЕДУЕТ

[В начало]

ССЫЛКИ НА СТАТЬИ

Статьи на русском языке

Введение в Visual Studio 2005 Team Edition (для профессиональных баз данных)
Брайан А. Рэнделл (Brian A. Randell)
Развертывание баз данных стало проще благодаря Visual Studio® 2005 Team Edition (для профессиональных баз данных) (или в сокращении - DB Pro edition). Если нужно создать или скомпоновать решения для подключения к SQL Server™ и управлять ими - этот инструмент для вас. DB Pro edition предлагает большое число функций, с помощью которых управление базами данных становится проще. Являясь компонентом Visual Studio 2005 Team Suite, DB Pro edition работает в связке со средствами управления исходным кодом Team Foundation Server, чтобы изменения в базе данных следовали сразу за изменениями исходного кода. DB Pro edition поддерживает развертывание механизма защиты "песочница" для баз данных и включает инструменты для сравнения баз данных, генерации данных, модульного тестирования и развертывания баз данных. Но что больше всего покоряет в DB Pro edition - это возможность использования всех функций совместно как части единого жизненного цикла развертывания базы данных. Прежде чем мы приступим к статье, следует прояснить еще несколько вещей. Во-первых, первый выпуск DB Pro edition поддерживает реляционные базы данных SQL Server 2000 и SQL Server 2005, но поддержка Analysis Services отсутствует. Поддержка других баз данных на платформе Майкрософт и других платформах планируется в следующих версиях через базовые модели поставщиков, но на момент написания этой статьи она недоступна. Во-вторых, хотя использование DB Pro edition в связке с Team Foundation Server расширяет возможности многих функций DB Pro edition, наличие Team Foundation Server не является необходимым условием для работы с DB Pro edition.

Особенности проектирования современных баз данных
Олег Сиротюк
Современные объемы хранимых данных, обязательные требования к их доступности и скорости обработки, динамика развития систем обуславливают важность исследования факторов, влияющих на качество баз данных (БД), лежащих в основе современных информационных систем.

PRB: SQL Server 2000 с пакетом обновления 2 (SP2) и ниже не поддерживается семейством Microsoft Windows Server 2003
Microsoft
SQL Server 2000 с пакетом обновления 2 (SP2) и более ранних версий не поддерживается операционными системами семейства Microsoft Windows Server 2003. При установке SQL Server 2000 на компьютер под управлением Windows Server 2003 появляется следующее предупреждение:

Как получить последний пакет обновления для SQL Server 2000
Microsoft
Корпорация Майкрософт распространяет исправления в виде пакетов обновления, служащих для модернизации соответствующего продукта. Помимо обновлений, в их состав могут входить средства системного администрирования, драйверы, а также дополнительные компоненты, упакованные для облегчения загрузки из Интернета. Пакеты обновления формируются по накопительному принципу (каждый из них содержит не только новые исправления, но и все исправления, включенные в пакеты предыдущих версий). Перед установкой последнего пакета обновления нет необходимости устанавливать предыдущие версии. Например, перед установкой пакета обновления 2 (SP2) для SQL Server 2000 не нужно устанавливать пакет обновления 1 (SP1) для SQL Server 2000.

SQL Server 2005 Best Practices Analyzer (February 2007 CTP)
Александр Гладченко
Сразу же, вслед за SP2, на сайте Microsoft стал доступен для скачивания предварительный дистрибутив инструментария генерации рекомендаций для внесения улучшений в конфигурацию SQL Server: SQL Server 2005 Best Practices Analyzer (February 2007 CTP). Теперь эта утилита может устанавливаться и на Windows Vista.

[В начало]

Англоязычные статьи

Majority Node Set (MNS) with only 2 nodes?
Rodney R. Fournier
A new quick fix is available from Microsoft for Windows Server 2003 or the R2 Edition - http://www.microsoft.com/downloads/details.aspx?FamilyID=ee26bcf1-42ac-4b33-b3d8-863ac089a1ab&DisplayLang=en

Server Clusters: Quorum Options
Microsoft Corporation
The goal of this document is to explain the two types of quorums available in Windows Server 2003 clusters - standard and majority node set (MNS) clusters. Currently, the standard quorum that is used by the Microsoft Cluster Service (MSCS) in Microsoft® Windows NT® Server 4.0 and Windows® 2000 Server family of products is sufficient for the majority of situations users will encounter. With the introduction of Windows Server 2003, Microsoft has introduced the MNS quorum, which is primarily for advanced users and other software vendors to create more sophisticated end-to-end solutions.

Try out the SQL Server 2005 DM Add-Ins for Office 2007
DMTeam
This tip shows you how to get all the pieces you need to try out the new SQL Server 2005 DM Add-Ins for Office 2007.

HOWTO: Detect the database is Merge Replicated?
Dan Shaver
Sometimes an application may want to know whether the database is in good shape in terms of Merge Replication especially when the merge replication is aborted by the user, or due to connection failure or many other reasons. Here are the things application should ideally check if the database is merge replicated correctly and is usable further.

Microsoft SQL Server 2005 Compact Edition Access Database Synchronizer (ADS) RTW
SQLCEBLOG
Today we are very pleased to announce the RTW release of Microsoft SQL Server 2005 Compact Edition Access Database Synchronizer (ADS) for which many of you been waiting eagerly.

Predictive Analytics for the Excel user ... and that means for you
Donald Farmer
I was speaking at a BI event in Ireland a couple of weeks ago. After one session, one of our partners decided to collar me with some friendly criticism of Microsoft's well-known slogan BI for the masses. "You can have all the dashboards and reports you want," he said, "But until I can open up Excel and do cutting-edge analysis right there, it's all just blowing smoke. Excel is where I do my work, and that is where my BI needs to live to be efficient. It is all well and good having data mining or some such on the server, but it's too complex. I need it in Excel, and I can't wait for that day to arrive." I could feel, every time he stressed Excel that he was mentally prodding me, with good humour I must say, until I got the point.

Import Header-Line tables into Dynamic Excel destinations I
Rafael Salas
I saw a post recently in the MSDN SSIS forum asking for help to build a package to loop through the rows of a 'Header' table. Then for each 'Header' row; get rows from 'Line' table and then import them into an Excel file, this is, generating a different Excel file for each Header-lines set.

Import Header-Line tables into Dynamic Excel destinations II
Rafael Salas
After talking about how to use a ForEach Loop container to iterate through a query result set in the first part of this post; I am going to show how to create a distinct excel file on every iteration dynamically.

Passing parameters to an OLE DB Command transformation (Using a CTE)
Rafael Salas
I must start this post by saying that I am not a big fan of using OLE DB Command. While this transformation may look appealing to some common ETL scenarios; it really slows down the overall performance as that command gets executed for every row that goes through the data flow pipeline. Anyway, I have been playing with it and realized that its SQL parser is just 'picky' and actually has problems when mapping parameters in half way complicated querys (Just as the OLE DB Source component).

SSIS Package Configurations using SQL Server table...an alternative to the Indirect method
Rafael Salas
Recently, I was involved in a discussion about how to get the SQL Server based configurations work using the indirect settings; since I have not had the chance of getting into it; I thought it would be helpful to share a technique that yields similar results.

SSIS: Loop through files that are within a date range (if date is part of the file name)
Rafael Salas
For those lucky ones that have to process file system files based on a date range AND the date is part of the file name here is one possible solution:

Tips about exam 70-431 (MCTS: SQL Server 2005)
Efran Cobisi
On Friday, 9 February, 2007 at about 11:30 AM I passed exam 70-431 and earned the title of MCTS: SQL Server 2005; now that the excitement is growing dim, I'd like to take the time to share my impressions on the new certification model embraced by Microsoft.

Multiple connections to a SQL Server Compact Edition database on a network share Part 2
Jim Wilson
I mentioned in my post yesterday that SQL Server Compact Edition doesn't support multiple concurrent connections to a database file located on a network share. In systems where we control the configuration avoiding this scenario is easy. It occurs to me, however, that many systems are end-user configurable which means that the user may choose a network share as the home for the database file for any number of reasons.

Mastering OLAP Reports: Extend Reporting Services with Custom Code
William Pearson
As I have emphasized throughout the articles of the series, the most powerful characteristic of Reporting Services is the unprecedented flexibility it offers us in creating reports specifically tailored to our business environments. It affords us the capability to innovatively employ one or more datasets in supporting myriad options, to use data groups and report items in all manner of combinations, and to extend data reporting with many features, from basic to advanced, including calculations, conditional formatting, and other options.

PowerSMO At Work Part I: DBA Scripts and Functions
Dan Sullivan
In my previous introduction to PowerSMO, I covered the most basic operations of PowerShell, PowerSMO, and WMI. This article will investigate some of the more powerful PowerShell features, in particular scripts and functions, and then use those with PowerSMO to manage the extended properties of SQL Server objects.

SQL 2005: Enter XML
Michael Coles
SQL Server 2005 has greatly expanded the capabilities of SQL Server in many different areas. One of those that has matured greatly is the integration of XML data inside SQL Server. Longtime writer and guru Michael Coles brings us the first part of a two part series on XML ion SLQ Server 2005.

SQL Server 2005: Intro to XQuery
Michael Coles
Longtime SQL Server expert Michael Coles brings us the second part of his his introductory look at XML in SQL Server 2005 with a short examination of XQuery, the way in which you can write queries on XML data.

Shred XML data with XQuery in SQL Server 2005
Tim Chapman
Last week, I outlined how to shred XML data into a relational format by using the OPENXML function in SQL Server. We learned that OPENXML is straightforward and simple to use, yet very memory intensive. This week, I discuss new functionality in SQL Server 2005 that allows you to shred XML data into a relational format without the intensive memory operations.

More on Disjoint Subtypes
David Portas
My post on Disjoint Subtypes generated some interesting feedback. I made a point of saying that the Products table in my schema is in BCNF. That's because the only determinants are supersets of {SKU}, which is the only key. The point I wanted to make was not about normalization as such but about the nature of SQL UNIQUE and PRIMARY KEY constraints. There are two such constraints in this case but only one key. In SQL the UNIQUE and PRIMARY KEY constraints are superkey constraints, not key constraints. In other words, they are unique but they are not required to be irreducibly so. There is no reason to doubt that Products is in BCNF, as long as you understand that a UNIQUE constraint is not the same as a candidate key (and a PRIMARY KEY is not the same as a Primary Key).

Trouble with CHECK Constraints
David Portas
ANSI/ISO SQL defines CHECK constraints very simply as: <check constraint definition> ::= CHECK <left paren> <search condition> <right paren>

What's coming in Windows Server 2003 Service Pack 2
Scott Lowe
Windows Server 2003 SP2 is coming-with all the improvements you've been missing. Scott Lowe gives you a sneak preview of some of the fixes, as well as operating system additions and enhancement tools, you can look forward to.

Different forms of CrossJoin
Mosha Pasumansky
This article is about different syntactical ways to write CrossJoin in MDX, all of which are completely equivalent from the functional and performance standpoint. Therefore, the article doesn't convey any practical information, and can be ignored. Readers curious about history of MDX can keep reading, however.

The Importance of the Trace Event Sequence Column and SQL Server 2005 SP2 Changes
Bob Dorr
Many of us are used to looking at a SQL Trace and making the assumption that the order of display is what occurred during the capture.

Replication and Database Mirroring (Failover) Support
Laxmi Narsimha Rao ORUGANTI & Balachandar P
Replication and Database Mirroring (Failover) support for Merge Replication between SQL Server 2005 SP1+ and SQL Server 2005 Compact Edition (Mobile Edition SP1+) is supported but it was not documented as the support was added after RTM and the documentation was not updated. Then comes for me an opportunity to BLOG about it : (apart from filing a DOC BUG). Here the story goes:

Derived Column
Phil Brammer
I wanted to post about some different ways to work with varying data types in the derived column transformation. Some basic stuff follows:

Generating Surrogate Keys
Phil Brammer
Generating surrogate keys is no sweat in SSIS. Though it does require a custom script to be written. (You might be able to use the Row Number Transformation)

A nice gift from SQL Server 2005 SP2 to sync developers
Rafik Robeal
If you haven't heard the news yet, SQL Server 2005 SP2 is out! Now if you are like me, thinking about data synchronization for the most part of your day, then you should be really excited about SP2. Let me tell you why.

What'dya mean I can't TRUNCATE Tables that have RI?
Brett Kaiser
So, we've gotten into the business of sanitizing or scrambling sensitive production data for development environments. This is, for the most part, was the direction to do this for mainframe flat files. Now comes along distributed environments, mostly 3rd party vendor applications on sql server. You should see some of the twisted things these apps do. Using reserved words as column names, creating tables with the same name but different owners..the list is long.

SQL Server 2005 Command Line Tool "SQLCMD" - Part II
Muthusamy Anantha Kumar
In Part I we saw a few ways of using the SQL Server 2005 SQLCMD command line utility. In Part II of this article, I illustrate how to use transact SQL files and generate output.

Scripting database objects a different way
Rob Farley
Sometimes you want to make changes to your SQL Server objects (like tables, indexes, whatever), and you figure that it will be worth recreating them with a different set of options. One way of doing this is to generate a script, and then use a text manipulator to change the script in a way that will make it just what you want.

SQL Server Security- The Crib Sheet
Robyn Page
For things you need to know rather than the things you want to know

SQL Server Error Handling Workbench
Grant Fritchey
Error handling in SQL Server breaks down into two very distinct situations: you're handling errors because you're in SQL Server 2005 or you're not handling errors because you're in SQL Server 2000. What's worse, not all errors in SQL Server, either version, can be handled. I'll specify where these types of errors come up in each version.

Quality Data Through Enterprise Information Architecture
Semyon Axelrod
Data quality is a well-known problem and a very expensive one to fix. While it has been plaguing major U.S. corporations for quite some time, lately it is becoming increasingly painful. Higher prominence of various regulatory-compliance acts, such as SOX, GLB, Basel II, HIPAA, HOEPA, and others, necessitates an adequate response to the problem. For a significant class of data issues (semantics), it is not possible to solve the "data-quality problem" by just working with data. Unfortunately, this solution is not commonly understood at all, but the results leave very little doubt about the current (pure-data) approach effectiveness. This discussion proposes how to deal with this problem through correct information architecture.

A Flexible Model for Data Integration
Tim Ewald and Kimberly Wolk
There are many challenges in systems integration for architects and developers, and the industry has focused on XML, Web services, and SOA for solving integration problems by concentrating on communication protocols, particularly in regard to adding advanced features that support message flow in complex network topologies. However, this concentration on communication protocols has taken the focus away from the problem of integrating data. Flexible models for combining data across disparate systems are essential for successful integration. These models are expressed in XML schema (XSD) in Web service-based systems, and instances of the model are represented as XML transmitted in SOAP messages. In our work on the architecture of the MSDN TechNet Publishing System (MTPS), we addressed three pitfalls. We'll look at what those pitfalls are and our solutions to them, in the context of a more general problem-that of integrating customer information.

Data Replication as an Enterprise SOA Antipattern
Tom Fuller and Shawn Morgan
As applications are envisioned and delivered throughout any organization, the desire to use data replication as a "quick-fix" integration strategy often wins favor. This path of least resistance breeds redundancy and inconsistency. Regularly applying this antipattern within an enterprise dilutes the original long-term goals of a service-oriented architecture (SOA). However, depending on the context in which it surfaces, data replication can be viewed either positively or negatively. To deliver to a service-oriented strategy successfully, enterprise architects need to draw from the successes and failures of other architectures. Architecture patterns, when discovered and documented, can provide the best technique for managing the onerous job of influencing change in your enterprise. We'll use both an antipattern and a pattern to describe how data replication can affect your enterprise architecture.

Patterns for High-Integrity Data Consumption and Composition
Dion Hinchcliffe
The challenge is to consume and manage data from multiple underlying sources in different formats, while participating in a federated information ecosystem, and while maintaining integrity and loose coupling with good performance. Here are some emerging patterns for the growing world of mashups and composition applications.

SQL Server 2005 Objects: T-SQL or SQL CLR?
Stefano Demiliani
One of the powerful features of SQL Server 2005 is the ability to write SQL Server objects (stored procedures, triggers, user-defined functions, user-defined aggregates, user-defined types) with managed code.

SQL Server 2005 Query Execution Plan "distributable"
Stefano Demiliani
Today I was doing a tuning operation for a query on a customer's database and, when asking to the System Administrator if he can send me the query execution plan, I've received on my mailbox a nice JPG image, a screenshot of the customer's desktop where the query execution plan was displayed. Ok... better than nothing... :P

Hide a SQL Server 2005 Instance
Stefano Demiliani
This is a problem that a customer had in these days: how can I hide a SQL Server 2005 Instance?

Best practice when optimizing indexes on SQL Server 2005
Stefano Demiliani
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.

Database Mail on a cluster
Tara Kizer
In our production environment, we have two 4-node clusters. One cluster runs at the primary site; the other cluster runs at our disaster recovery site. Each cluster is running 11 SQL Server 2005 instances.

More different ways to script database objects a different way
James Green
Rob Farley posted a very interesting codegen post on scripting objects this morning, and as a reforming codegen junky I just couldn't let it go without comment :-)

The Nordic Object/Relational Database Design
Paul Nielsen
The New Object/Relational Database Design (Nordic), like many tools, is not the most fitting or expedient solution for every kind of database problem. However, the object/relational hybrid model can provide more power, greater flexibility, better performance, and even higher data integrity than traditional relational models, particularly for databases that benefit from inheritance, creative data mining, flexible class interactions, or workflow constraints. Discover some of the innovations that are possible when object-oriented technology is modeled using today's mature relational databases.

Desktop Database Dance
Greg DeMichillie
A new version of SQL Server provides the answer to a desktop database engine. For many years, Microsoft had two database engines, the Jet engine used by Access and Visual Basic, and SQL Server. But Jet has been riding off into the sunset for the better part of six years, helped along by the move to the .NET Framework and retirement of VB6. That's left a hole in Microsoft's developer platform. The company needed a database engine that can be deployed with a desktop application without having to license and deploy a full-blown copy of SQL Server. Now, Microsoft is finally filling that hole with a new version of SQL Server.

Sharp Words with Microsoft's Erik Meijer
Mary Jo Foley
The architect for the Microsoft SQL Server Group is interviewed about the various research Sharp languages and their impact on future software development and programming. The architect for the Microsoft SQL Server Group is interviewed about the various research Sharp languages and their impact on future software development and programming.

Relational Databases 101
William Vaughn, Peter Blackburn
In this sample chapter, William R. Vaughn gives you a kick-start on designing relational databases that can perform better, be easier to maintain, and be more successful thanks to a combination of formal rules and informal suggestions to normalize your database.

Introducing SQL Server 2005 Reporting Services
Bret Updegraff
Microsoft SQL Reporting Services is a full-featured, server-based, end-to-end reporting application that includes services, tools, and APIs to create, publish and manage reporting solutions. SQL Reporting Services was originally slated for an initial release with SQL Server 2005. Thanks to the convergence of customer demand and product readiness, RS was introduced as an add-on to SQL Server 2000 at the beginning of 2004. This revised edition book covers the SQL Server 2005 release of Reporting Services, and compares, when relevant, the changes from the SQL Server 2000 release.

MSMQ Triggers
Richard Costall
In this chapter from "Pro MSMQ: Microsoft Message Queue Programming," find out what MSMQ triggers are and how they access message queues. Also learn how to install, create, and use triggers.

SQL Server Urban Legends Discussed
Bob Dorr
I have been answering questions on two of the most misunderstood 'SQL Server Urban Legends' frequently again so I decided to make a post about them.

SQL Server 2005 Performance Statistics Script
Bart Duncan
Many of you have used the SQL Server 2000 blocker script (see KB 271509) to help troubleshoot performance issues in SQL 2000. If you haven't used it, you probably use a script of your own that take a similar approach, polling sysprocesses and virtual tables to detect blocking and other resource contention issues. In PSS we've started using a new performance troubleshooting script for SQL 2005; we call this one our "Perf Stats Script", since it's useful for troubleshooting lots of issues in addition to blocking. The script takes advantage of the extremely rich set of data exposed by the new Dynamic Management Views in SQL Server 2005. We've found that the raw DMV output can be difficult for many people to interpret without a lot of help, so the script attempts to join the relevant DMVs together and present a simplified view of the most relevant data. If you like, feel free to check the script out and use it as a starting point when troubleshooting your own perf issues:

Dynamic Management Views and Functions in SQL Server 2005
Decipherinfosys
Dynamic management views & functions in SQL Server 2005 is such a nice addition - Oracle has had these since a long time. These return the server state information and are very useful for troubleshooting purposes. There are server scoped and database scoped dmv's. Here is a breakdown of the high level categories:

How to move objects from Schema to another in SQL 2005
Mohamed Sharaf
If you are building CLR stored procedure and you want this stored procedure to be part of a schema called xyz. When you write the name of the procedure in the SqlProcedure attribute "xyz.myproc" and deploy this procedure, the visual studio deployed it with dbo schema and the name of the procedure will be dbo.xyz.myproc.

Merge Replication not sending all changes to the subscribers
Steve Wright
I have been using SQL Server 2005 merge replication within my current project. It is a centralized Merge push topology using a custom RMO application with over 350 subscribers but that is the subject for another post…

SQL Cmd Issues
Patrick Wright
The script below should give you the servername for each server you connected to. But what if the second one is offline? Or perhaps you don't have login permission to it? Then it should ignore the error and go to the third one right? No it doesn't. For some reason on error ignore seems to do just about nothing. I've tried it in lots of different methods in this script and I can't get it to ignore and keep going. Anyone else run into this?

I've Got the XML - Now What?
David McKinney
Getting a result set of XML is the easy part, but what about exporting the data in a human readable format? Most DBAs don't spend a lot of time formatting XML output, but it doesn't hurt to know how. New author David McKinney brings us a technique for generating an XML article and then using SSIS and XSL to transform it into an HTML page.

SQL Server XML Performance Tips
sql-server-performance.com
When using the FOR XML clause in your Transact-SQL applications, don't include the XMLDATA option. The XMLDATA option returns additional XML schema data that generally is not needed. Because of this, using this option adds extra overhead to your server and network connection, reducing performance. [2000] Updated 1-10-2006

Find the Knee of the Curve
Linchi Shea
Whether you are load testing SQL Server or evaluating the I/O performance of a storage subsystem, you need to make sure that you cover the entire spectrum of the load levels--from light and moderate all the way to heavy and saturated--before you draw any conclusion. This is especially important when you are doing performance comparison. Relying on a few isolated data points can easily lead you to erroneous conclusions.

Parse the sqlio.exe Output
Linchi Shea
Let's say you run sqlio.exe as follows: D:\sqlIO -kW -t32 -s30 -dE -o1 -fsequential -b8 -BH -LS Testfile.dat

Eliminating database recovery
Jim Damoulakis
Maintaining old copies of databases can become absurdly wasteful. Providing protection and ensuring timely recoverability of databases has always represented a unique challenge for IT. Unlike file system data, databases usually appear to backup applications as large monolithic containers, and as data volumes have increased, so has the problem.

Should You Upgrade Your Current SQL Server Cluster to a SQL Server 2005 Cluster?
Brad M. McGehee
If your organization is like many organizations, it may have some older version SQL Server clusters in production. If so, at some point you will have to make a choice about how to upgrade them to SQL Server 2005. Your available options include:

ADS - Automatically Starting the synchronization between device and desktop
Manish Agnihotri
I have received many question on how to automatically start synchronization between the device and desktop when using ADS, hence i am posting this blog which outlines "one" way to do this.

08.04.08 is SQL2K day
James Rowland-Jones
Well it isn't likely to cause quite the same stir as Y2K but I thought it was a significant date for everyone's diaries...

Build Your Own Analysis Services Cache-Warmer in Integration Services - Part 2
Chris Webb
As I mentioned in my last post on this subject, where you've got a large amount of memory to play with it's hard to fill up the cache when you have a cache warmer which relies on previously captured queries - you'd need to spend an awful long time capturing queries to get enough and most of the time your users won't have that many queries which they run unchanged on a regular basis. So I got thinking about how I could warm the cache with very large amounts of useful data as easily as possible. First of all I thought it might be a good idea to start at the highest levels of granularity in a cube, run an MDX query which returned all the data in that slice, and gradually work my way down to lower levels of granularity doing the same thing. But this seemed a bit hit-and-miss: users don't always want these higher levels of granuarity and in any case these queries are usually going to be well-served by aggregations. So I came up with the idea of using the AS query log (as used by the Usage Based Optimisation wizard) as a way of capturing the granularities that users were actually querying and using that data as the basis for further cache warming. The information in the query log has the advantage of being based on the queries users are running, but also it's sufficiently generalised: if someone runs a query for a specific Product's sales in a particular Store, say, then that will appear in the query log as a query on at the Product/Store granularity, so if you then try to cache all the data at that granularity then any subsequent queries which ask for a different Product at a different Store will also be served from cache. Note that this is rather more 'experimental' than the approach I described in my first post on cache warming, so I wouldn't recommend anyone went out and put this in production without extensive testing; if you do use any of these ideas, though, I'd be very interested to hear from you.

Problem found with SQL SP2 and Maintenance Plans!
Ted Malone
A potentially bad problem has been discovered with SQL Server SP2 and Database Maintenance Plans. From the official notice:

Maintenance Plan Changes in SQL Server 2005 SP2
Sqldbatips
Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Estimating vardecimal storage savings in all databases
Sqldbatips
One of the new features in SQL Server SP2 is the vardecimal storage format which can be used to significantly reduce the space required by decimal and numeric columns in a table at the cost of a small CPU overhead. The storage savings must be balanced against the additional CPU usage that is required to convert the storage format of the rows every time that they are accessed. Additionally, writing to a table that is using vardecimal storage format can slow performance because of an increased number of page splits. Refer to Books Online for all the caveats and notes about using this new storage format (available only in Developer,Enterprise and Evaluation editions).

Profiler and Perfmon Co-relation in SQL Server 2005
Decipherinfosys
One of the neat features introduced in SQL Server Profiler in version 2005 is the ability to co-relate profiler results with perfmon results. This is good because it gives us the ability to correctly see what was going on in the system. There may be times when you see spikes in Performance Monitor on your SQL Server machine such as a high CPU usage, large amounts of memory consumption, or overall slower performance etc.. Before SQL Server 2005, you would have to capture a trace, use sysprocesses, syslockinfo and other system related data from SQL Server and capture your Performance Monitor logs. After doing all this, you would need to manually reconcile what happened between them to figure out why performance was suffering in the system. Manual re-conciliation of the wait events and queues really became very cumbersome.

Using the Search Feature
Buck Woody
SQL Server Books Online is an absolutely huge resource. With over 58,000 pages that change several times a year, it's like getting all of the books at one time that you are ever going to use from Grade-School through College. Not only that, it is used by beginners, intermediates and experts; by developers, DBAs and architects, and is used as the definitive resource for design experts.

[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string
Jon Galloway
Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string. I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).

Building a WebMethod FORMAT=NONE Stored Procedure
Jimmy Wu
Commonly for Web Services, the exposed interfaces (webmethods) must conform to some pre-defined contract. To assist customers developing web services which conforms to these contracts, SQL Server 2005 Native Web Services provides the ability for customers to construct and format the output XML themselves. To do this, the Stored Procedure (or CLR Stored Procedure) must return only one column of type 'nvarchar' (any length including "max") with the column name of "XML_F52E2B61-18A1-11d1-B105-00805F49916B".

Using SQL Trusted Connections from a machine not in a domain
Barry Dorrans
This month I've been doing some work in WinPE. For those that don't know what it is it's a stripped down version of Windows for Preinstallation Environment(s). It provides a bootable CD (or RIS image delivered over the network when a bare metal machine is booted, assuming it supports PXE). The environment is rather limited, but can be built to support Windows Shell Scripts, ADO and HTA.

Why cannot i GRANT EXECUTE permission on assemblies anymore?
RaviR
Just a quick note that this is by design and no you don't need to use it. What are we talking about ?

Database Mail - the send mail queue is part of "your" database transaction
Tony Rogerson
While writing some notification stuff around the conflict detection in merge replication I got a problem - the sp_send_mail I was executing was not sending out the email even though I know email was working; basically in the merge stored procedure conflict resolved, if you decide to choose the subscriber as the winner and the subscriber has removed the row then for some reason the agent rolls back the stored procedure transaction (enough on that, I shall leave merge conflicts for another day).

Launching Report Builder from the command line
Bob Meyers
A number of people have asked if it is possible to launch RB from the command line. While it is in fact possible, it is an unsupported feature in SQL Server 2005. That said, here's how to do it. You just need to specify what report server to connect to, like this:

How to use URL Access in Reporting Services SharePoint integration mode
Russell Christopher
URL Access still works if you've put your SSRS instance into MOSS integration mode, it just takes a bit of extra work. Here's an example using the following assumptions:

Collecting Requirements For Key Information
Brett Kaiser
I've been asked to assist (this time BEFORE Project initiation for a change) in the developmennt of a new application. The Business Liason/Tech group that is doing this has been collecting requirements (basically reviewing an EXCEL Spreadsheet on steriods) and is coming up with a data model. We will be doing a model review, but I was able to guide them in how to record the information so I can leverage the data to generate the tables. I've done this several times already, and they are still fine tuning the table defintion. I then take that document (Excel, again) and generate the DDL and drop it into ERWin (I'll post that code later).

SqlCredit - Part 2: Creating the Database, Tables, CRUD Procedures
Rob Garrison
This is part 2 of a series. If you have not read part 1, you can find it here.

Database Design: A Point in Time Architecture
Arthur Fuller
Point in Time Architecture (PTA) is a database design that guarantees support for two related but different concepts - History and Audit Trail.

Some Usages for XML
Yousef Ekhtiari
While SQL Server 2005 has greatly expanded the XML capabilities of the platform, many DBAs are still not familiar with or comfortable with using XML in their coding. Yousef Ekhtiari brings us a new article that looks at a basic use of XML in inserting data into a table.

Turn XML into relational data with OPENXML
Tim Chapman
Starting with SQL Server 2000, Microsoft began to provide support for XML data. This support has been significantly extended for SQL Server 2005, allowing for XML data columns, XML variables, and XML indexes.

T-SQL vs SQL CLR on loops
Stefano Demiliani
Looping between a big number of records is a CPU-intensive operation that can have a significant impact on your database performance. I've talked in the past that with SQL Server 2005 you can use SQL CLR (.NET) for improve your performances on CPU-intensive operations, such as looping, iterations, calculations etc.

Failure Trends in a Large Disk Drive Population
SQL
The Google engineers published a paper on Failure Trends in a Large Disk Drive Population. Based on a study of 100,000 disk drives over 5 years they found some interesting stuff:

Interviewing - The Technical Screening Phone Call (Part 3)
Louis Davidson
If your resume seems to be up to snuff, the next step for many companies is the screening call. This is more or less a mini-interview, generally done on the phone to just get an idea of your knowledge. There are different ways to do this, such as having a list of canned questions to ask, but I like to go from the resume, seeing if the resume matches the person, plus a number of general purpose questions based on the actual job being interviewed for. Like for the BI Position we are interviewing heavily for right now, I toss in the typical questions about BI, like "What is a fact table", "What is a dimension", and "What is the difference between a snowflake and a star schema" just to make sure the basics are covered

Uncovering Missing Indexes
Kalen Delaney
SQL Server 2005's missing-index metadata can help improve query performance. As you're probably aware, one of the most important tasks that the SQL Server query optimizer performs is to determine which index (or set of indexes) to use for each table in a query. As each query is being optimized, SQL Server keeps track of indexes that the optimizer determines might have been useful by establishing the best possible index for each search argument in your WHERE clause and for each lookup done through a join. SQL Server 2005 then makes information about missing indexes available to you through two components. First, SQL Server provides a set of metadata objects to supply information about missing indexes. Second, for each query, information about missing indexes is available in the XML query plan. I'll explain the missing-index metadata, which can help you improve query performance, and next month I'll provide more details about working with the missing-indexes feature, including accessing missing-index information in your XML query plans. . . .

Automate Date/Time Conversions
Susan Perschke
This extensible, flexible, data-driven utility can handle all your date-formatting needs. SQL Server offers two native data types for storing date and time data: datetime and smalldatetime. Both store the date and time together, with datetime offering more range and precision than smalldatetime. Table 1, compares these two data types. Because SQL Server stores date and time information together, working with date and time data-even with the help of built-in T-SQL conversion functions-is challenging. Storing date and time information in the same column means you have to parse the date information if you want to separate it into individual elements for data input, date math, or data retrieval. T-SQL was designed as a data-manipulation language and doesn't have rich formatting capabilities. Ideally, you should handle date formatting at the client. For example, SQL Server 2005's common language runtime (CLR) integration lets you build solutions that use client APIs and Microsoft .NET programming languages, which have simple, functional tools for such tasks. But if your organization is one of the many still running SQL Server 2000, you can use T-SQL to build a flexible, reusable solution for converting date/time data into the various formats you need. Let's briefly look at some date/time-conversion examples, then see how you can auto-mate such conversions by building an extensible, data-driven utility based on a table and a simple stored procedure. . . .

Preparing the SQL Server 2005 Clustering Infrastructure
Brad M. McGehee
Before you even begin building a SQL Server 2005 cluster, you must ensure that your network infrastructure is in place. Here's a checklist of everything that is required before you begin installing a SQL Server 2005 cluster. In many cases, these items are the responsibility of others on your IT staff. But it is your responsibility to ensure that all of these are in place before you begin building your SQL Server 2005 cluster.

Enabling a Trace Flag
WARDY IT
In SQL Server 2000 setting a trace flag was really obvious. In Enterprise Manager you could right click on a Server and select properties and then add a Startup Parameter. In SQL Server 2005 the process to set a trace flag is not quite as obvious, as the Startup Parameters are not set in SSMS but in SQL Server Configuration Manager.

[В начало]

ФОРУМ SQL.RU

Самые популярные темы недели

Ваше мнение об упражнениях SELECT на http://sql.ipps.ru
Вышел русский SQL Server 2005!!!
То ли лыжи не едут, то ли я... в общем Suspect. Help, одним словом
Возможно ли
Хитрый запрос, слабо?
Обнавление страницы клиента из триггера.
Хранение файлов в базе данных
Помогите с запросом.
шаблон для вложенных хп в MSSQL 2005
После установки SQL 2005 SP1 не запускается служба SSIS
bat Гат.
иерархия + xml
Жуткие Тормоза в 1С на SQL Server
Кодировка при отправке почты с помощью "xp_smtp_sendmail"
TopN vs SET ROWCOUNT
File 'c:\a.csv' does not exist
Кодировка 866 >> 1251 для dbf в DTS
Защита от маски-шоу и прочих
Контрольная точка для структуры базы
DTS: Loop, DBF

[В начало]

Вопросы остались без ответа

Не могу просмотреть базы Manager-ом
Трабла с кодировкой
Через ODBC линкую MySQL к MSSQL как LinkedServer
6553 в DTS
Полнотекстовый поиск в pdf (вопрос)
СЕМИНАР: Microsoft Business Intelligence в деталях для ИТ - профессионалов
Как поднять Exception в SSIS?
Администрирование MSSQL с КПК
Помогите разобраться с ошибкой в ХП?
Установка Microsoft SQL Server Developer Edition 2005
ФСТЭК сертифицировала Microsoft SQL Server 2005
Sql server CE

[В начало]

КНИГИ

Microsoft SQL Server 2005

Уильям Станек

Твердый переплет, 544 стр., 2006 г. Издательство: Русская редакция. ISBN 5-7502-0281-X

Книга адресована системным администраторам Microsoft SQL Server 2005, администраторам Windows, желающим изучить Microsoft SQL Server 2005, администраторам, переходящим па SQL Server 2005 с предыдущих версий Microsoft SQL Server и других платформ, а также менеджерам, отвечающим за управление базами данных или занимающимся другими вопросами работы Microsoft. SQL Server 2005 в организации. Данная книга - краткий и исчерпывающий справочник, посвященный Microsoft SQL Server 2005. Здесь рассматриваются все основные вопросы, связанные с выполнением стандартных задач администрирования серверов баз данных, в том числе настройка, оптимизация работы, обеспечение безопасности и многие другие насущные вопросы.

[В начало]

#347<<  #348  >>#349
Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013