СОДЕРЖАНИЕ

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

Уважаемые подписчики!

Неожиданно для себя, мы узнали, что наша рассылка учувствовала в первом этапе фестиваля золотых рассылок Subscribe.ru и в своей конкурсной номинации по итогам голосования вышла в следующий круг. Осталось определить "лучших из лучших". Пригашаем Вас, если Вы сочтёте нашу рассылку этого достойной, проголосовать за неё.

С уважением, А. Гладченко и В. Степаненко

[В начало]

СТАТЬИ

Репликация слиянием - ручное управление диапазоном identity

По материалам статьи Paul Ibison: Merge Replication - Manual Range Handling
Перевод Маргариты Баскаковой

Для репликации транзакций и репликации моментальных снимков закономерно, что свойство identity, существующее на изданной таблице, не передается подписчику. Просто это не требуется, поскольку подписчик не предназначен для того, чтобы добавлять строки самостоятельно. Репликация слиянием, однако, предназначена для независимого, автономного добавления данных, и в этом случае, свойство identity будет передаваться. Поэтому возникает вопрос - как управлять диапазонами identity и гарантировать, что не будет наложений в значениях identity при синхронизации. Существует 2 варианта - автоматическое и ручное управление диапазонами. SQL Server может автоматически управлять диапазонами identity в репликации слиянием, но этот способ имеет плохую репутацию так как может повлечь за собой некоторые проблемы. Хотя верно то, что когда выделенный диапазон заканчивается раньше, чем произойдёт синхронизация, могут быть осложнения, в большинстве случаев их можно просто избежать: свойство identity накладывается на колонку с целочисленным типом данных int, который принимает значения в диапазоне от -2 147 483 648 до 2 147 483 647, тогда действительно, если у вас нет невероятно большого числа подписчиков, выбор большого диапазона значений на подписчике должен гарантировать, что выделенный диапазон не закончится. Однако в форумах часто задается вопрос - как вручную администрировать диапазоны identity и таким образом брать управление ими на себя. Эта статья представляет собой пошаговую инструкцию применения подобного алгоритма на практике.

(1) Метод без первоначальной синхронизации данных

Во-первых, выберите алгоритм, который Вы будете использовать для гарантии того, что диапазоны каждого из узлов не будут пересекаться. Существует несколько методов (см. сайт Michael Hotek's). Например, предположим, что Вы имеете издателя и трёх подписчиков. Издатель может иметь положительные четные номера, первый подписчик положительные нечетные номера, второй подписчик отрицательные четные номера, третий подписчик отрицательные нечетные номера. Используя этот метод, каждый узел имеет примерно один миллиард возможных значений.
Как только алгоритм выбран, удостоверьтесь перед публикацией таблицы, что начальное значение (Identity seed=2), и шаг приращения (Identity increment=2), установлены на издателе правильно [Рис. 1.1].

[Рис. 1.1]

Если после публикации таблицы Вы попробуете изменить значение identity на издателе или подписчике это закончится сообщением об ошибке.

'tCompany' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tCompany' because it is being used for replication.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]] Невозможно удалить таблицу 'dbo.tCompany', так как она опубликована для репликации.

На издателе начальное значение (seed) и шаг приращения (increment) применяются, как только таблица опубликована, и это происходит раньше, чем у него появляется первый подписчик.
Теперь займёмся таблицей, участвующей в репликации слиянием. Убедитесь, что оставлен пустым checkbox в свойствах статьи на вкладке "Identity Range", который фактически разрешает ручное управление диапазоном identity [Рис. 1.2].

[Рис. 1.2]

Затем перенесите таблицу на подписчика. Вы можете использовать DTS, резервное копирование/восстановление, BCP, прилинкованные сервера - в зависимости оттого, что Вы предпочитаете. Таблица может быть передана, как только у неё будет столбец uniqueidentifier, с установленным свойством rowguid, в противном случае при синхронизации Вы получите ошибку, приведенную ниже:

Error: Invalid column name 'rowguidcol'.

Пока с публикуемой статьёй всё нормально, эта ошибка не может проявиться, поскольку таблица уже имеет столбец rowguid или публикация этой таблицы добавит его к таблице.
Затем, на подписчике повторно устанавливают текущее и начальное значение identity. Почему это необходимо? Рассмотрим случай, когда есть 4 записи:

ID	Company	rowguid
1	HP	{5B9D38B2-5089-4C42-816B-48D0157ADA34}
2	DH	{5A17C1B2-C045-4279-87BE-38F5F4959C0B}
3	IBM	{B2FF82DA-5384-42AF-92F0-4029A5908395}
4	Microsoft	{AB637574-7EBF-4950-9865-5378166BEB96}

Если издатель имеет начальное значение (seed) = 2 и приращение (increment) = 2, а первый подписчик имеет начальное значение = 1 и приращение = 2, в обоих случаях, следующая запись будет иметь ID 6. Это потому, что текущее значение identity - 4, таким образом, в обоих случаях, следующее значение будет 4 + 2. Значит, нужно запустить на подписчике:

DBCC CHECKIDENT (tCompany, RESEED, 5)

чтобы начать правильный отсчет. Это можно сделать, используя скрипт из моментального снимка, после того как он был применён, но начальное значение и приращение должны быть изменены вручную, и мы, в таком случае имеем один дополнительный ручной шаг - это самое легкое решение. Фактически этот шаг может быть сделан вручную, и начальное значение с приращением на подписчике можно оставить такими же, как и на издателе, а так как в этом примере значение identity повышается парами, то будет достаточно переопределения текущего значения identity. Однако это может запутать администратора, так как потом всегда придётся анализировать начальное значение и приращения таблицы, чтобы определить значения устанавливаемых границ.
Наконец выполните инициализацию подписчика без первоначальной синхронизации данных. Если Вы делаете это с помощью скриптов, то воспользуйтесь хранимой процедурой sp_addsubscription с параметром sync_type=none, или через GUI выбрав опцию, приведенную ниже [Рис. 1.3].

[Рис. 1.3]

Следует помнить о том, что все статьи, опубликованные для репликации слиянием вместе с рассматриваемой таблицей, в этом случае будут так же без первоначальной синхронизации. Поэтому имеет смысл изолировать эту статью, и любые связанные с ней публикации.

(2) Использование @creation_script в sp_addmergearticle

Второй метод GUI полностью не поддерживается. Он требует разработки отдельного сценария для создания таблицы на подписчике:

exec sp_addmergearticle @publication = N'TestIdentitiesXXX', 
   @article = N'tCompany', 
   @source_owner = N'dbo',
    @source_object = N'tCompany', 
   @type = N'table', 
   @description = null, 
   @column_tracking= N'true', 
   @pre_creation_cmd = N'drop', 
   @creation_script = 'C:\misc\tCompany.sql',
   @schema_option = 0x00, 
   @article_resolver = null, 
   @subset_filterclause =null, 
   @vertical_partition = N'false', 
   @destination_owner = N'dbo', 
   @auto_identity_range= N'false', 
   @verify_resolver_signature = 0, 
   @allow_interactive_resolver =N'false', 
   @fast_multicol_updateproc = N'true', 
   @check_permissions = 0

Список аргументов стандартный, за исключением аргумента @creation_script, который определяет файл, используемый при создании таблицы, и аргумента @schema_option, который предписывает использование упомянутого выше файла. Этот скрипт(C:\misc\tCompany.sql), созданный с помощью Enterprise Manager на издателе, просто создаёт скрипт таблицы, но с начальным значением (seed) identity, отличным от издателя:

[ID] [int] IDENTITY (2, 2) NOT FOR REPLICATION NOT NULL

После инициализации текущее значение identity необходимо изменить еще раз, чтобы повторно определить отправную точку.
После того, как будет запущен snapshot agent, текстовый файл скрипта будет передан в repldata share. В этом файле можно будет вручную изменить параметры identity до инициализации подписчиков. Теоретически, такие изменения могут быть сделаны, с помощью системной хранимой процедуры, которая позволяет изменить детали существующей статьи - sp_changemergearticle. Однако, как только один из подписчиков синхронизировался, изменится сценарий создания, использующий этот метод, что лишит законной силы эту подписку, и Вы будете обязаны повторно инициализировать всех остальных подписчиков (@force_invalidate_snapshot = 1 и @force_reinit_subscription = 1). На практике файл может быть изменен только вручную.
Любые другие статьи можно будет добавлять к этой публикации стандартным методом.

Заключение

Были представлены два альтернативных метода, позволяющие организовать ручное управление диапазоном identity. Первый, в отличие от второго, легко настроить при помощи графических инструментов. В то время как второй метод позволяет более гибко добавлять новые статьи к существующей публикации.

[В начало]

Обзор SQL Server 2005 для разработчика баз данных

По материалам статьи Matt Nunn, Microsoft Corporation: An Overview of SQL Server 2005 for the Database Developer
Перевод Виталия Степаненко

Microsoft Corporation

Июль 2004

Относится к:
Microsoft SQL Server 2005
Разработка баз данных SQL Server
Microsoft .NET Framework
Microsoft Visual Studio 2005
Microsoft ADO.NET 2.0

Описание: этот документ содержит обзор новых возможностей разработки баз данных в SQL Server 2005 (14 печатных страниц).

Содержание

Новая парадигма разработки баз данных
Интеграция с .NET Framework
Технологии XML
Новая среда разработки
Улучшения в языке
Заключение

Новая парадигма разработки баз данных

С выходом Microsoft SQL Server 2005 Beta 2 в мире разработки баз данных начинаются изменения. Если Вы разработчик баз данных, то теперь Вы можете создавать код в соответствии с его функциональностью, иметь доступ к данным во встроенных форматах, таких, как XML, и создавать сложные системы, управляемые сервером баз данных. Разработка баз данных становится более интегрированной, чем это было раньше, и все необходимые инструменты находятся прямо у Вас под рукой.

Эта статья обсуждает некоторые новые возможности SQL Server 2005, которые позволят Вам создавать приложения базы данных другого уровня.

Интеграция с .NET Framework

С выходом Microsoft SQL Server 2005 Beta 2 программисты баз данных могут использовать все преимущества библиотеки классов Microsoft .NET Framework и современных языков программирования для внедрения новой функциональности на сервере. Используя интеграцию с common language runtime (CLR), Вы можете создавать Ваши хранимые процедуры, функции и триггеры на языке .NET Framework, если захотите. Языки программирования Microsoft Visual Basic .NET и C# дают возможность работать с объектно-ориентированными конструкциями, структурированным управлением исключениями, массивами, пространствами имен и классами. Кроме того, .NET Framework предоставляет тысячи классов и методов, имеющих большие встроенные возможности, которые Вы легко можете использовать на стороне сервера. Многие задачи, которые было тяжело выполнить в Transact-SQL, могут выполняться гораздо легче с использованием управляемого кода; кроме того, теперь доступны два новых типа объектов базы данных - агрегаты и пользовательские типы. Теперь Вы сможете лучше использовать знания и навыки, которые Вы приобрели во время написания встроенного кода. Одним словом, SQL Server 2005 Beta 2 позволяет вам расширять возможности сервера базы данных для более простого выполнения вычислений и операций.

[В начало]

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

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

Использование SQLParameters в VB.NET/C#
dotScott
VB: При использовании Microsoft Data Access Application Block для доступа к базе данных Microsoft SQL Server 2000 и CommandType - встроенная процедура, возможна необходимость передавать входные параметры этой встроенной процедуре. Эта статья подготавливает сценарий и показывает процесс успешного заполнения DataGrid или DropDownList...
Системы обработки информации - язык баз данных SQL со средствами поддержания целостности
Перевод: Сергей Кузнецов
SQL: Этот стандарт определяет синтаксис и семантику двух языков баз данных: Язык определения схем (SQL-DLL) для объявления структур и ограничений целостности базы данных SQL. Язык модулей и манипулирования данными (SQL-DML) для объявления процедур базы данных и выполняемых операторов в конкретной прикладной программе базы данных....
Способы подсчета количества записей в каждой из таблиц базы данных
N/A
MSSQLServer: В этой стате рассматриваются несколько способов, с помощью которых можно подсчитать количество записей в таблицах базы данных MS SQL Server...
Развертывание .NET Framework и MDAC
Dwight Johnson
C-sharp: Раньше я никогда не пытался создать Setup-проект в Visual Studio. И когда я сейчас решил-таки этим заняться я решил также установить себе .Net Framework и MDAC. Зачем упрощать жизнь?...

[В начало]

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

Writing to a File Using the sp_OACreate Stored Procedure and OSQL
Zach Nichter
Recently I had an engagement where I needed to collect DBCC SQLPERF data at 15 second intervals and wanted to write the results to a file. I’ve always used OSQL for this type of thing but wanted to see if there was another way so I searched the Internet to see if DBA’s were using any different methods to accomplish this task. In my search I found some articles relating to the sp_OACreate and the sp_OAMethod stored procedures and thought I would see if using these stored procedures would be better for performance than my old method
Partitioned Tables and Indexes in SQL Server 2005
Kimberly L. Tripp
Table-based partitioning features in SQL Server 2005 provide flexibility and performance to simplify the creation and maintenance of partitioned tables. Trace the progression of capabilities from logically and manually partitioning tables to the latest partitioning features, and find out why, when, and how to design, implement, and maintain partitioned tables using SQL Server 2005. (41 printed pages)
Data Mining: An Introduction
Mike Chapple
By this point in time, you've probably heard a good deal about data mining -- the database industry's latest buzzword. What's this trend all about? To use a simple analogy, it's finding the proverbial needle in the haystack. In this case, the needle is that single piece of intelligence your business needs and the haystack is the large data warehouse you've built up over a long period of time
Bitmask Handling, part 3: Logical operators
adam machanic
It's been longer than I hoped since my last installment on bitmask / big number handling. Life caught up with me and I've had many thankless tasks to catch up on. But that's over now and I'm back to the general slacking that typifies my days, so welcome to Part 3, handling logical operators
What is Service Broker?
Write Ahead Blog
A number of large database applications require asynchronous and distributed execution. Most loosely-coupled distributed systems provide scale out and resilience by queuing operations. Queues are used extensively in applications such as order entry systems, stock trading, warehouse querying and travel reservation systems. Writing queued distributed applications is a challenging task since it involves reliable network communication, integrity and ordering of messages, tolerance to process and system failuresa and coordination of transactions with the database. While most of these tasks can be achieved by using specialized messaging systems such as MSMQ and MQSeries, the close association between handling data and communicating messages invokes the need for an integrated solution
An XSLT 2.0 business case?
Enjoy Every Sandwich
Kurt Cagle makes one. Sort of. While I think Kurt is spot-on about many things -- primarily that XSLT 2.0 has real value -- I don't agree with this: [C-Omega]... It presupposes that the appetite for XQuery will be strong enough that they can essentially build a hybrid language around it, though after having written two books on XQuery that have between them garnered less than the production costs for the books in question I'm much less inclined to agree, especially as XPath2/XSLT2 becomes much more functionally oriented
Otey on Stinger Vs. Yukon and the Art of War
Enjoy Every Sandwich
Sun Tzu writes in The Art of War: "A defeated army first seeks to do battle, then obtains conditions for victory." It's amazing how true this really is. Adam points us at an op-ed piece that Michael Otey has written comparing DB/2 "Stinger" to SQL Server 2005 with respect to CLR integration. While I don't know that I'd blame the apparent brain damage shown by these poster(s) on Java, it really does seem like they have an agenda themselves, rushed into battle then tried create conditions where they might be seen as being "right." Here's what I posted back as comments
Oracle BI Beans connecting to Microsoft XMLA Provider
Mosha Pasumansky
I never thought I would mention the word “Oracle” in my blog, but Matt Rittman, whose blog I am watching, just posted Juliad Ford's description of how he connected Oracle BI Beans to to Microsoft XMLA Provider - and I think this is too cool to pass on. Oracle was always very hostile to XMLA standard, so it is pretty interesting to watch Oracle technology interoping with XMLA, and even more interesting to see it interoping with Microsoft implementation. So enjoy MDX query accessing Foodmart 2000 cube data inside BI Beans component :)
Microsoft SQL Server 2005. To GUI or not to GUI? That is the Question
Larry Chesnut
See how that trip to England affected me? Have you ever wondered how to do something in SQL Server 2005 using TSQL, but wasn’t sure how. You may have done something 10 times from the gui, but now you want to code it. Well, one really cool thing about Yukon is that there are buttons to script out just about everything. But if that doesn’t meet your needs, you can always fire off a Profiler trace while you do your operation one more time through the gui. Then you can see what the gui was issuing behind the covers. Well, I did that last week in England. A workshop attendee from Israel pointed out a possible irregularity and so I wanted to see for myself
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
William Pearson
In this article, we will return to a function we introduced in an earlier article, CROSSJOIN(), whose mission it is to combine two sets of dimension members into one. We will discuss how CROSSJOIN() works in general, and then discuss how its performance can become an issue in medium to large-sized data sets. We will then undertake a multiple-step practice example intended to assist us in gaining an understanding of the issues, before exposing approaches to minimizing the performance overhead that we can encounter in using CROSSJOIN() to reach our reporting and analysis needs
Dynamic defrag of all indexes for a given table
Robert Davis
Running 'DBCC INDEXDEFRAG' on a table can be a big performance boost without the overhead of the full 'DBCC DBREINDEX' on high traffic databases. However 'DBCC INDEXDEFRAG' carries the overhead of having to specify each index by name or ID rather than just the table. This means your maintenance tasks must be updated if you delete old or add new indexes to a table
Heterogeneous data replication using SQL Server
Barrie Sosinsky
There are some good reasons why you may want to explore using heterogeneous replication, but the best reason is that it allows an administrator to offer SQL Server to clients whose companies have a critical investment in other data systems
When to use data replication
Baya Pavliashvilli
As it relates to SQL Server, replication is a way of keeping data synchronized in multiple databases. However, if you have numerous database servers that need to be involved in various types of replication, this seemingly simple task can quickly become complex. Before you make the decision to replicate your database, take a look at this tip from InformIT which examines some of the reasons to use replication
Replicating SQL Server over the Internet
Greg Robidoux and Jeremy Kadlec
I am planning to have one SQL Server database on my company's LAN and another database on a server managed by our ISP (i.e. in a seperate location). I would like to replicate these databases across the Internet. Initially there is only a small amount of data (40-50 MB), and we have a 2 MB link to the Internet through our ISP. Is this a sensible idea, and if so, then what tips or suggestions could you offer with regards to replication methods, VPNs and general ideas for starting such a venture?
Moving SQL-replicated databases
Michael Hotek
Is there a way to move a SQL-replicated database from one server to another without dropping the publication? I have SQLCE setup with SQL Server 2000; I want to redo the server, but need to move my replication to a temp server and then back. The problem is that I do not want to drop my subscription and recreate it. It needs to stay the same. It isn't possible. You have to drop it and recreate it. This is because the replication engine, via all of the metadata, is tied into the specific server endpoints in order to be able to connect and perform its work
Generating a Bar Code
Masar Ivica
Sometimes you have to start from beginning, almost from a DOS, especially if you task sounds like "make a simple barcode generator". Naturally, I started with most complicated ideas, to make a RTF (it is not short for read the f*** manual) or a PDF as an add-on to previous article. Other ideas were to create HTML tables with cells black & white backgrounds, or to use GIFs but it is almost complicated like drawing shapes in RTFs or PDFs. But first of all
XML Options in Microsoft SQL Server 2005
Microsoft Corporation
Three options for handling XML data in the Visual Studio 2005/SQL Server 2005 environment are discussed, including usage scenarios and guidelines to help you choose between them. (34 printed pages)
Mine Data from Text Files
DMTeam
This tip describes how to use text files as data sources for Data Mining. In many data mining projects it is common to start with data that resides in flat text files. Through the flexible data access mechanisms for SQL Server 2005 data mining and some help from the Microsoft Access data provider, we can create models that are sourced directly from text files
Tracking the pesky appdomain
Bob Beauchemin's Blog
People (especially DBAs) want to see what those pesky appdomains are doing in SQLCLR. Back in beta1 there was a system function, master.sys.fn_appdomains(), that showed which appdomains were running and which assemblies were loaded in the appdomains, number of bytes used, etc. In beta2 this view stopped working and, although you can watch appdomains being created and destroyed in the SQL Server log, I'd always missed master.sys.fn_appdomains().
SQL CLR and XML: Saw the woman in two carefully
Enjoy Every Sandwich
One highly underrated Warren Zevon song -- "For My Next Trick I'll Need a Volunteer" -- offers us an insightful piece of wisdom about this topic
SQLCLR: Why use Sql[type] instead of Plain Old [Type]
Enjoy Every Sandwich
I got nailed with a couple of good questions tonight that I could have answered better. One of them was “why would you choose to use SqlString vs. String in a SQLCLR object?”
Updating a local table from company tables
Rudy Limeback
My company stores data in SQL tables. These tables contain a lot of data which is of no interest to me. I have developed procedures which will extract the data of interest to me from several tables and put it into a new SQL table. Since every month several hundred new entries are added to the original tables, how can I extract just the new entries and add them to the new SQL tables I created?
The HAVING and GROUP BY SQL clauses
Ian Gilfillan
This month's topic is one that often baffles inexperienced SQL coders. We will look at the GROUP BY clause, and then the difference between conditions placed in the WHERE clause, and the HAVING clause. I have used MySQL to test all of the examples, but most of them should work without any problems on other DBMS' too. We are going to be using the following test data
SQL Server Performance Top Tip: Degrees of Parallelism and a Degree of Uncertainty
Mat Stephen's WebLog
Max degree of parallelism is an advanced configuration option in SQL Server about which many question have been asked and much written. In this blog entry I hope to offer some insight and certainty as to what this option does and how it should be used
Optimizer Join Methods
Randy Dyess
SQL Server uses three join operators to process joins within a query. For smaller result sets, the optimizer might use a nested loop join but for larger results, a hash join or merge join may be used. The Query Optimizer always tries to identify the fastest way to join tables and will sometimes rearrange or sort tables in order to utilize a faster join method
Dealing with embedded sort orders
Arthur Fuller
You may not want to sort columns alphabetically or numerically if they have a special format. Here's how you cope with unusual sort orders
How to Performance Tune Microsoft SQL Server During Setup
sql-server-performance.com
The sort order you choose when installing SQL Server can affect its performance. The fastest sort order you can choose is the binary sort, although the results it produces may cause problems in your client applications. The default sort order of "dictionary order, case-insensitive, is the next fastest, and the one you should probably choose. The "dictionary order, case-insensitive, accent-insensitive, uppercase preference" and the dictionary order, case-sensitive" are even slower yet. Choose the sort order that best meets your performance and application needs
Dealing with very large bitmasks
adam machanic
Continuing in my series of things you should probably not do in SQL Server but sometimes have to, I'm going to do a few posts on dealing with very large bitmasks
Correction on bitmask handling
adam machanic
In the article on handling bitmasks I posted the other day, I made a fatal error in the splitBitmask function. The function treated the low byte as the first byte, instead of the high byte. Therefore
Dedicated Admin Connection (DAC) from more than just SQLCMD command line
Larry Chesnut
A fellow alumnus from my Microsoft PSS days recently turned me on to a neat little trick I want to share with you. Using the GUI called SQL Server Management Studio (SSMS) he showed me how to make a Dedicated Admin Connection. Boy, this blew me away. I am still trying to determine why this is even possible, but as long as it is in the product, I’ll take it
Activating stored procedures asynchronously
Rushi Desai
I was tempted to post a HelloWorld sample for Service Broker, but since Roger Wolter's article already provides that, I decided to avoid the redundancy. Instead I shall describe how to setup async execution using Service Broker's activation mechanism. Even if you think you'd never want to send messages in a database, I highly recommend copy-pasting the code from the link above into your SQL Server 2005 Beta 2 to see how 'cool' this really is
Sort OLAP Metadata With ADO MD.NET
Andrew J. Brust
Need to manipulate or display the structural content of your cube in addition to its data? ADO MD.NET will do the trick. It isn't only an API for running MDX queries. Using ADO MD.NET with Analysis Services 2005 is a great way to get all sorts of metadata from your cubes, including many of the new OLAP objects introduced in this latest version of the product
Monitoring Blocks
Leo Peysakhovich
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on different client computers. Some of the actions needing locking protection may not be obvious, for example, locks on system catalog tables and indexes. Most blocking problems happen because a single process holds locks for an extended period of time, causing a chain of blocked processes, all waiting on other processes for locks. There are many blocking scenarios such as submitting queries with long execution times, applications that are not processing all results to completion, etc. The application code itself must be designed appropriately and monitored for efficiency. In fact, most experts agree that as much as 70 to 80 percent of performance and blocking problems are caused by improperly coded database applications. SQL is the primary culprit. And this is why one of the common tasks in multi-user multi-database environment is to monitor the processes that are blocked by another process for more than X number of seconds
Trace Messages Part IV: Getting the Trace Results
Andrew Novick
The current theme is about application trace messages. So far we’ve created AppSession rows to organize a session, AppSessionTrace rows with the trace lines, and AppSessionMeasurement rows to store metrics. At the end of each script that uses the usp_AppSession_* stored procedures you will see a few lines of script that retrieve the session, trace, and metric information. Here’s a sample
How to Best Implement a SQL Server Performance Audit
sql-server-performance.com
If you have gotten this far, you have done a lot of reading. In this final article on how to perform a SQL Server Audit, we will take a look at some best practices about how to best implement a SQL Server Performance Audit. You will want to read this before you begin any actual performance audits on your SQL Servers
SQL Server 2005. System.Transactions, promotable transactions, and composition
Bob Beauchemin's Blog
There's been a lot of interest in the new System.Transactions.dll assembly lately. Especially from users of SQL Server 2005. This is based around two functionality points
Deadlock Detection using Profiler in SQL Server 2005
ExtremeExperts.com
The amount of features introduced in SQL Server 2005 are numerous. I think we have taken a tour of some T-SQL enhancements, DTS a.k.a. SSIS, XML and others before. But in this article we will take a look at another toolset that is available in SQL Server 2000 world but has been enhanced in this version, the Profiler. In this article we would look into an specific area of profiler enhancements, detecting dead locks in SQL Server 2005
Secure options for remote SQL Server administration
Serdar Yegulalp
In this two-part series, Serdar Yegulalp explains how to remotely administer SQL Server without compromising network security. In part one below, he describes two methods for providing secure administrative access to remote employees. In part two, he'll examine practical problems you may encounter when remotely administering SQL Server and how to work around them
SQL: Removing duplicate rows
Rudy Limeback
I have a table with primary key on empid and deptid. Accidentally the key got dropped and some duplicate records were inserted into the table. Can I delete the duplicate records using a sinqle query so that I can create the primary key again?
SQL Server 2000 Reporting Services Activation Process
Matthew Hofacker,Brian Hartman
SQL Server 2000 Reporting Services uses cryptography to protect account data. Learn how SQL Server 2000 Reporting Services generates and stores the keys required to encrypt data
Finding the SQL Server Agent ErrorLog
Andrew Novick
The theme this month is logging messages so that they get to the proper human. In the last issue I discussed the SQL Server ErrorLog and showed a stored procedure, dba_ErrorLog_Search that can be used to search the current log. It turns out that SQL Server Agent also has an error log and it's the subject of this article
How SQL Server Determines an Execution Plan Using Available Indexes and Statistics
Nils Bevaart
As a SQL Server DBA, one of my tasks is to monitor the performance of our databases. Our primary database holds the data of over 18 million people. A web-based application queries the database with a variety of search options. When examining the performance of these queries, most performed very well. But on the other hand, a few queries have resulted in a time-out
Microsoft Provides Workaround for Row-Retrieval Problem
Editors
Microsoft has provided a workaround for a problem that occurs when you retrieve rows from a SQL Server 2000 database by using browse mode in a client application. If you retrieve rows from a SQL Server database by using browse mode in a client application and the rows contain null values, you might not be able to identify the appropriate rows and perform the correct manipulations to the rows in the result set. Additionally, when you try to update the rows by using the additional key column metadata that the SQL Server program returns, the rows aren't correctly updated in the SQL Server database.
Avoiding Application Failure
Umachandar Jayachandran
Congratulations to Emmanuel Nanchen, an analyst and developer for Manpower in Gland, Switzerland and Erik McKibben, a DBA for the Port Authority of Allegheny Country in Pittsburgh, Pennsylvania. Emmanuel won first prize of $100 for the best solution to the February Reader Challenge, "Avoiding Application Failure." Erik won second prize of $50. Here’s a recap of the problem and the solution to the February Reader Challenge
Code/Module Signing in SQL Server 2005
Randy Dyess
One of the nice security features found in SQL Server 2005 is the ability to digitally sign a stored procedure. Code signing or module signing provides the ability to provide for a more granular control on the ability to access SQL Server tables
Basics of the Service Master Key in SQL Server 2005
Randy Dyess
Now that SQL Server 2005 is just around the corner, more and more information on its new capabilities is being released. During the upcoming months it is likely that you will hear the term Service Master Key more and more in relation to SQL Server 2005’s data encryption support
SQL Server Performance Top Tip: SQL Server User Mode Scheduler (UMS) – understanding it can explain some odd performance
Mat Stephen's WebLog
When performing batch jobs where you wish to run processes in parallel it is important to understand how the scheduler works
Comparing Service Broker with other messaging technologies
Rushi Desai
An often asked question about SQL Service Broker is how it compares with other products offered by Microsoft such as MSMQ, BizTalk server, WSE and its successor Indigo. So I thought I’ll take a shot at answering this politically provocative question. [These are my personal opinions]
Bitmask Handling, part 4: Left-shift and right-shift
adam machanic
Quick installment this time. Left-shift and right-shift operators. Left-shift and right-shift are integral to binary mathematical operations as they have two important qualities: Left-shifting a bitmask once multiplies by two. Right-shifting once divides by two
Code Generation: Using SQL to Generate SQL and Other Code
Haidong Ji
As you gain more experience as a DBA or developer, you undoubtedly will gather a bunch of tips and tricks that could increase your productivity and make your day fun. One such trick I've learned as a DBA is code generation using SQL
SSIS: Accessing a recordset in a script task
Jamie Thomson's Blog
It is possible to populate an SSIS variable of type "Object" with a recordset using the "Recordset Destination" data flow destination component. Thereafter there are various ways to access the contents of this recordset, one of which is to use a Foreach loop to loop over the recordset (using the "For each ADO Enumerator") and then use a Script Task to access the enumerated record
The Microsoft Solution Accelerator for Business Desktop Deployment, the Microsoft Solutions Framework and the Microsoft Operations Framework
Mark Wilson's Blog
Last week, I spent three days learning about the Microsoft Solution Accelerator for Business Desktop Deployment (BDD). According to Microsoft
Microsoft SQL Server 2005. How do you pick a column size for encryption?
Larry Chesnut
While I was in London delivering a Yukon Ascend class, I was looking at a demo script concerning encryption for a table. My attention eventually focused on creating the table and the column for social security numbers, a typical piece of data that would do well being encrypted. It then dawned on me, how big should I make the column? Although the SSN is only 11 characters long, when I tried using varbinary(11), that clearly didn’t work
The ACID Model
Mike Chapple
The ACID model is one of the oldest and most important concepts of database theory. It sets forward four goals that every database management system must strive to achieve: atomicity, consistency, isolation and durability. No database that fails to meet any of these four goals can be considered reliable
Performance of aggregating data from lower levels in MDX
Mosha Pasumansky
In the "MDX Solutions" book, at pages 69-70, George Spofford discusses the task of computing expression [Units Sold] * [Last Update of Price] at the leaf-level cells and then aggregating the result up to the higher levels. George gives the following formula to do the aggregation in MDX
SQLOS's memory manager and SQL Server's Buffer Pool
Slava Oks's WebLog
SQLOS's memory manager consists of several components such as memory nodes, memory clerks, memory caches, and memory objects. Fig 1. depicts memory manager components and their relationship
SQLOS - unleashed
Slava Oks's WebLog
With SQL Server 2005 Beta 1 and Beta 2 you might have noticed that there is no ums.dll in the bin directory. The reason is simple it is no longer exists. In SQL2000 ums.dll provided SQL Server with user mode non-preemptive scheduling. So what happened in latest version? Have we removed non-preemptive scheduling? The answer is no. Yukon continues relying on non-preemptive scheduling. As adequate research shows for RDBMS engine to meet performance and scalability requirements it needs to leverage non-preemptive scheduling
Look at Memory Leaks - Classify and Identify
Slava Oks's WebLog
In my previous post I talked about type of Memory Pressures. Today I would like to talk about related subject – Memory Leaks. Before you continue reading please make sure that you are familiar with how memory is managed on Windows
Memory Pressure - Classified
Slava Oks's WebLog
So far I touched on subjects that have been widely discussed in the industry. Today I would like to talk about a subject that you would hardly find information about: memory pressure.. On a surface the subject appears simple but in reality this is not the case
A look at Windows Virtual Memory mechanisms (continuation of "A look at Virtual Address Space - VAS")
Slava Oks's WebLog
As I promised last time here comes next post on memory J. Remember, my eventual goal is to reveal how memory management works in SQL Server but for you to really appreciate it, I think, you do need to get good feeling on how Windows manages memory. Understanding details is great however at this point I want you to understand the concepts! In some cases I on purpose skip the details because right now they are not important and many of them can be changing from one release to another. So lets continue!
Tracking VAS in Yukon, cont...
Slava Oks's WebLog
Some of you could have gotten bored with my previous post http://blogs.msdn.com/slavao/archive/2005/01/27/361678.aspx. Yea, I know that VAS subject was covered multiple times and by multiple authors in the past and you probably know about it more than you ever wanted to know :-). However please bear with me. I would like all of us to be on the same page. I do need to cover some of OS internals so that I can cover the subject that all of you want to learn more about: SQL Server memory management. But I will continue with the subject I started next time :-)

[В начало]

ФОРУМ SQL.RU

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

Ваше мнение об упражнениях SELECT на http://sql.ipps.ru
Поймем как работает или Зачем MsSQL использует версии данных ?
Составной кластерный индекс - вопрос оптимизации
Импорт таблиц из одной БД в другую. Help!
Момент Connect'a к SQL-серверу
Auto create statistics
запуск mcdtc в win98
Больше книг хороших и разных!
Анонс. Новая версия SQLExecMS.
Какие проблемы с MS SQL Server 2005?
Не работает бэкап
Странные дела твоеятся в процедуре
MS SQL умеет запускать DCOM объекты?
Дерево
64 + 32 разрядные версии
Таки случилось
Вычитание одного select из другого
побайтное/побитное сравнение BLOB полей
Как запросом перенести данные из временной таблицы в dbf файл? Хелп!
Два update по PK

[В начало]

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

установка сервера используя iss файлы
кодировка в SQL Server CE
Почему закрывается приложение
Merge replication. Несколько публикаций на одну таблицу
Как правильно написать запрос FTS
vb + ado + mssql
Ошибка при установке Reporting Services
xp_makecab - Можно ли добавить в архив каталог
Системы учета населения
Подключение базы
Дисковый массив в новом железе again
Где теряется почта?
Расчет параметров сервера для MSSQL
Пара вопросов про связку JDBC-MS SQL

[В начало]