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

СОДЕРЖАНИЕ

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

 

СТАТЬИ

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

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

Аудит страниц

В SQL Server 2000 SP4 и SQL Server 2005 расширены возможности аудита страниц. Включение динамического флага трассировки -T806 приводит к тому, что все физически считанные страницы подвергаются глубокому DBCC - аудиту сразу после завершения чтения. Эта проверка выполняется в то же время, что и PAGE_AUDIT, а также другие логические проверки страниц.
Это ещё один способ обнаружения повреждений страниц данных, который использует область страницы, не входящую во всегда проверяемый при физическом чтении заголовок. Например, если включить флаг трассировки -T806, то область строк на странице будет проверяться на нарушение их последовательности.
Впервые, аудит страниц появился в SQL Server 2000 SP3 v.8.00.0937. Для получения более подробной информации, см. следующую статью Microsoft Knowledge Base: FIX: Additional diagnostics have been added to SQL Server 2000 to detect unreported read operation failures.
Обратите внимание: Включение аудита страниц увеличивает нагрузку на процессоры и снижает общую производительность SQL Server.
В SQL Server 2005 появилась возможность сверки контрольных сумм, которая может использоваться вместо аудита страниц. Защита контрольной суммой гарантирует, что каждый бит на странице соответствует тому, что храниться на долговременном носителе. Зачастую, проверка контрольных сумм в SQL Server 2005 является лучшим решением, чем постоянный аудит и проверка целостности данных. Однако, аудит страницы позволяет обнаруживать повреждения, которые уже были сохранены на долговременном носителе, причём, даже в тех случаях, когда физическая последовательность страницы не была нарушена. Служба поддержки Microsoft SQL Server в своей практике сталкивалась с подобными примерами. В одном таком случае, внешняя расширенная хранимая процедура осуществляла наложенную запись на странице данных, которая к тому времени уже была отмечена как "грязная". Контрольная сумма рассчитывалась для уже поврежденной страницы, после чего страница была сохранена на диск. Чтение такой страницы при включённом аудите страниц показывало ошибку, при этом, контрольная сумма совпадала. Если оказывается, что серверу не удаётся обнаружить проблему с помощью сверки контрольной суммы, но он может её обнаружить с аудитом страниц, рассмотрите возможность включения режима сверки контрольной суммы в оперативной памяти, и режима принудительных кратких блокировок, что должно Вам помочь определить источник наложенной записи (scribbler).

Аудит журнала

В SQL Server 2000 и 2005 добавлен флаг трассировки -T3422, который включает аудит журнала регистрации транзакций. Аудит журнала существенно упрощает разрешение проблем, связанных с искажением записей журнала транзакций, но повышает нагрузку на сервер, вызванную операциями журналирования.

Контрольная точка

SQL Server 2005 позволяет теперь управлять порождаемым командой CHECKPOINT вводом-выводом, и лучше обслуживает ввод-вывод автоматического исполнения контрольных точек. Для получения более подробной информации об изменениях в команде CHECKPOINT и выборе значения для определяющего продолжительность исполнения контрольной точки параметра, см. SQL Server 2005 Books Online.
В Microsoft пришло много предложений по реализации более динамичного алгоритма контрольной точки. Например, это могло бы быть полезным во время завершения работы SQL Server. Для баз данных с очень высокими требованиями к доступности, более агрессивная контрольная точка способствовала бы уменьшению объема работы и времени исполнения стартовой регенерации (recovery).
Объём активности операций журналирования определяет частоту запуска для базы данных контрольной точки. Записи журнала транзакций имеют исчисляемую в миллисекундах стоимость. После каждой записи в журнал, прогнозируется стоимость исполнения регенерации, начинаемой с последней контрольной точки. Если заданная продолжительность регенерации превышена, вызывается контрольная точка. Это позволяет удерживать время исполнения стартовой регенерации в рамках заданного значения продолжительности регенерации.
Ниже представлены основные правила работы контрольной точки. Термин "Задержка" указывает на время, прошедшее с момента начала записи, и до того момента, пока она не будет считаться законченной процессом контрольной точки.

Действие

Описание

Ручной запуск контрольной точки с указанием продолжительности

Задержка ввода-вывода установлена в значение по умолчанию - 20ms. При остановке сервера, параметр продолжительности устанавливается в 100ms.
Максимальный объём ввода-вывода будет соответствовать большему из результатов этих двух формул:

  • Число зафиксированных буферов / 3750;

  • Число планировщиков * 80;

Объём ввода-вывода постоянно корректируется, чтобы прокачка через буферный пул была соразмерима с прошедшим и заданным временем.

Ручной запуск контрольной точки без указания продолжительности
- или -
Автоматический запуск контрольной точки в зависимости от активности работы с базой

Задержка ввода-вывода установлена в значение по умолчанию - 20ms. При остановке сервера, параметр продолжительности устанавливается в 100ms.
Максимальный объём ввода-вывода будет соответствовать большему из результатов этих формул:

  • Число зафиксированных буферов / 3750;

  • Число планировщиков * 80;

  • Минимальный объём установок ввода-вывода равен двум.

Объём ввода-вывода корректируется таким образом, чтобы запись успевала проходить в заданное время.

Для любых вызовов контрольной точки

Когда контрольная точка достигает порогового значения объёма ввода-вывода, она уступает ресурсы одному из ещё не законченных запросов ввода-вывода.

Если продолжительность не задана или контрольная точка исполняется автоматически

Процесс контрольной точки отслеживает присущий ей временной интервал обслуживания ввода-вывода. Это может влиять на объём запросов ввода-вывода, если время ожидания запроса ввода-вывода на запись для контрольной точки превышает установленную продолжительность. Поскольку процесс контрольной точки периодически активизируется на протяжении всего времени работы сервера, объём ввода-вывода динамически выбирается таким, чтобы обеспечить необходимое время отклика, не превышающее установленное значение. Если объём ввода-вывода становится настолько большим, что превышается допустимое время задержки, контрольная точка вносит коррективы в свою работу, чтобы снизить своё влияние на работу системы в целом.

Ручное управление контрольной точкой через указание продолжительности

Когда контрольная точка в процессе своей работы обнаруживает, что время её работы близко к установленной продолжительности, она уступает ресурсы до их высвобождения, как это показано в предыдущей таблице, или до тех пор, пока контрольная точка не обнаружит, что весть объём ввода-вывода завершён. Если весь объём ввода-вывода завершён, контрольная точка возвращается к первоначальной задаче ввода-вывода.

SQL Server 2005 SP1 учитывает вероятность непрерывного запуска контрольной точки

В SQL Server 2005 SP1 немного был изменён алгоритм работы контрольной точки. SQL Server 2005 не добавляет временные интервалы между вводом-выводом. В связи с этим, процесс контрольной точки может закончиться перед самым началом следующего запуска. В Service Pack 1 добавлены соответствующие задержки, которые стараются насколько возможно выдерживать продолжительность. Хотя это и не является рекомендуемой практикой, но администратор может отключить автоматический запуск контрольных точек и использовать ручное управление контрольными точками с указанной продолжительностью. Запуск контрольной точки в ручную, в непрерывном цикле и с заданной продолжительностью тоже может обеспечить непрерывное обслуживание контрольной точки. Делать это можно очень осторожно, потому что контрольные точки будут преобразованы в последовательную форму, и это может повлиять на другие базы данных, а также операции резервного копирования. Кроме того, необходимо будет организовать процесс контрольной точки для всех баз данных.
Обратите внимание, что в SQL Server 2005 Service Pack 1 включена заплатка для очень редкой ошибки, связанной с работой контрольной точки. Заплатка помогает тогда, когда контрольная точка получает очень маленькое окно, за которое она не успевает сбросить буфер на диск. Это может привести к повреждению данных. Для предотвращения этой проблемы, установите SQL Server 2005 SP1.

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

[В начало]

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

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

Как перенести данные?
Немиро Алексей
Данный обзор посвящен переносу данных с одного сервера на другой с использованием утилиты DTSWizard, которая входит в состав MS SQL Server Management Studio™.

Худшие методы (MS SQLServer) - Часть 1 очень длинной серии!
Andy Warren
В нашей работе мы тратим много времени на разговоры о "Лучших методах" - способах сделать что-то, что в течение долгого времени, оказывается самым эффективным (или точным, или каким-либо еще). Есть пара проблем, связанных с Лучшими методами. Первая - это то, что нет никакой "книги" о них; они разбросаны по сотням книг и веб сайтов, и часто вам нужно лишь одно предложение из всей этой информации. Другая проблема заключается в том, что оценка того, что считать Лучшим методом, не вполне ясна, т.к. это зависит от вашего отношения к решению проблемы, вашего опыта и вашей ситуации. Наконец, иногда Лучшие методы (давайте использовать сокращение ЛМ) могут быть западней, мешающей вам искать подходы, которые бросают вызов ЛМ, но могли бы стать идеальным решением вашей проблемы.

Добавляет (модифицирует) указанный пользовательский ODBC DSN-источник для доступа к MS SQL-серверу
Ksergey
В качестве справки по работе с ф-цией sqlconfigdatasource использовались источники, ссылки на которые есть в описании

Кумулятивный пакет заплаток (build 3152) для SQL Server 2005 Service Pack 2
Alexander Gladchenko
Этот новый кумулятивный пакет выпущенных после SP2 заплаток интересен прежде всего тем, что он один подходит для всех языковых версий SQL Server 2005 (включая русскую) и может быть удалён нормальным образом (как и build 2153), т.е. как заплатки операционной системы; через "Панель управления" (Control Panel), "Установка и удаление программ" (Add or Remove programs).

Приборная панель производительности SQL Server 2005
Alexander Gladchenko
Microsoft SQL Server 2005 Performance Dashboard Reports предназначен для контроля и получения необходимой для разрешения проблем производительности информации, собираемой с ваших серверов баз данных SQL Server 2005. Приборная панель работает с SQL Server и клиентскими компонентами Management Studio, для которых был установлен SP2 или последующий кумулятивный пакет обновлений.

Выложен исправленный SP2 для SQL Server 2005
Anton Belousov
5 марта 2007 на нашем цетральном сайте был выложен новый файл с SP2 для SQL Server 2005. В предыдущей версии была обнаружена критическая для работы приложений проблема, связанная с изменением логики работы задач очистки старых файлов в планах обслуживания (Maintenance Plans) и пакетах SQL Server Integration Services. Подробное описание проблемы тут: http://support.microsoft.com/kb/933508. Кроме того, была создана специальная заплатка, которая исправляет указанные проблемы на системах, где SP2 уже был установлен, и эта же заплатка была включена дополнительной частью обновления в новую версию SP2: 9.0.3042.1.

[В начало]

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

SSAS Partition Slicing
Eric Jacobsen
SSAS uses partitions to contain cube data. Small cubes might use only one, but for non trivial cubes, cube designers will create partitions based on ease of managing data and to split groups of data. This document discusses how the server uses "slices" to examine partitions at query time. The slices are stored as a range of internal surrogate keys, and can be thought of as a very high level index.

SSIS: Iterate over a package programmatically
Jamie Thomson
Today a colleague was asking me how to access the tasks and components within a package using code. Accessing the collection of tasks in the package is fairly easy however accessing the collection of components in a data flow task isn't quite so simple. The reason is that the data flow pipeline is a COM component. Thankfully Microsoft provide a managed implementation of the COM interface called Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe in order that we can get inside the data-flow. You need to reference Microsoft.SqlServer.DTSPipelineWrap.dll in order to access it.

SYSK 300: Why TABLESAMPLE Is Not Returning Specified Number of Rows
Irena Kennedy
One of the new features in SQL 2005 is the clause that allows you to get a random set of rows. The syntax supports specifying either a percentage or a number of rows to return:

Instant Initialization - What, Why and How?
Kimberly L. Tripp
Instant Initialization is a new feature of SQL Server 2005 that is based on an NTFS feature that was added to Windows XP (and therefore is also available in Windows 2003 Server). It's a feature that's seemingly simple; it allows file allocation requests to skip zero initialization on creation. As a result, file allocation requests can occur instantly - no matter what the file size. You might wonder why this is interesting or why this make a difference? Most file allocation requests are small requests, with small incremental changes (like .doc files, .xls files, etc.) but database files can be rather large. In fact, they should be rather large as pre-allocation of a reasonable file size is a best practice to reduce file fragmentation. Additionally, autogrowth causes performance delays (more so in 2000 than 2005) but it's generally something that you want to avoid when possible. As as result, database creation times can take minutes to hours to days, depending on file allocation request. But - it's not just for database creation. ALL file requests can leverage this feature: file creation for a new database, adding a file to an existing database, manually or automatically growing a file and (IMO - the best) restoring a database where the file (or files) being restored does not already exist. The reason I think the last feature is the best is that it can reduce downtime if a database is damaged and allow you to get back up and running more quickly. This is especially important for databases that cannot leverage partial database availability, which is an Enterprise Engine feature. So, to give you some motivation, here is a test that I performed just to have some interesting and comparable numbers.

Moving databases around - what are your options and across what versions?
Kimberly L. Tripp
I had a discussion earlier today (with Paul Randal) about many misconceptions that exist about upgrading databases and more importantly, about "downgrading" databases. Really, the issue is that I've heard people get frustrated when they find that things like backup/restore works FROM SQL 7.0/2000 TO SQL Server 2005 but not the other way around - even if the database is in SQL Server 2000 (80) compatibility mode. First and foremost, compatibility mode only affects parsing, query processing, and general data manipulation; it does not affect physical storage (well, there's more to it than that but that's a general overview). When you upgrade a database to SQL Server 2005, you WILL benefit immediately from changes in the storage engine, etc. regardless of compatibility mode. Compatibility modes are there to give you time in updating/upgrading your code - if/when necessary. Most code will work when upgrading but some code may not be supported because of changes to keywords, syntax changes, etc... The best thing to do is check your application compatibility with the Upgrade Advisor. I did a a couple of webcasts on Installation/Upgrade as part of my 11-part series on TechNet. See the blog entry for the entire series here. Part 3 and part 4 are focused on Installation and Upgrade and their associated blog entries have a lot of additional links (including links to the Upgrade Advisor as well as a series of things you might want to do before you upgrade). Also, be sure to checkout the upgrade site off of the main Microsoft SQL Server site.

MDX Clauses and Keywords: Use HAVING to Filter an Axis
William Pearson
In this lesson, we will explore the HAVING clause, which makes its debut in the MDX language with Analysis Services 2005. As we shall see, HAVING does not endow us with any capabilities we might not have achieved in other ways, with approaches that were fully available before Analysis Services 2005. What HAVING offers us is a way to apply a filter that is, perhaps, more easily understandable for a reader. More importantly, it offers us another means of manipulating context from the perspectives of our queries. Such manipulation can mean more optimal processing, and the sort of finesse that we can apply easily to an existing query structure - often without a rewrite, and more as an addition to syntax. These are the kinds of scenarios within which one can easily envision parameterization constructs, among other extended concepts in the Analysis Services and the reporting layers within the integrated business intelligence solution.

A Little Bit Scripting Saves the Day
Linchi Shea
Recently, I got sucked into importing Excel sheets into a database. I had thought, well that's easy with a tool such as SQL Server 2000 DTS Import/Export Wizard or SQL Server 2005 Import/Export Wizard. Needless to say, the Excel import business ran into a snag. With the error messages from the wizard not being really helpful, I ditched the wizard and wrote the following little ADO.NET program to read the Excel cells and dump them to a text file for a quick bcp import:

What are SLAs and why are they important?
Paul Randal
(In the UK now hanging out with Kimberly and Tony Rogerson before teaching a Masterclass tomorrow in Reading. Then it's off to Copenhagen for SQL Server Open World, with a little R&R in London beforehand and Copenhagen afterwards, before we fly back to the US on Sunday. The weather here is actually better than in Seattle!)

Oh no - my backup is corrupt too! Help!
Paul Randal
Funny how a bunch of people all seem to have the same problem at the same time. Maybe it's just that people don't want to talk about corruption until someone else does - it's like a dark secret that once let out of the bag - everyone talks. The common HA/DR issue over the last few days on the newsgroups and forums seems to be not just data corruption but on restore corruption in the restored backup.

How To Make A FileGroup Read Only in SQL Server 2005
SQL
How to make a filegroup read only in SQL Server 2005? This question popped up today on tek-tips. This is how you do that: First we will create a new database named TestFilegroup. Next we will add a filegroup named Test1FG1 which contains a file named test1dat3.

SQL Server 2005 Performance Troubleshooting - Introduction
Glenn Berry
I have been hanging out on the MSDN SQL Server Database Engine forum lately, answering questions. I have seen lots of questions along the lines of "My SQL Server is running slow. What could be wrong?" These types of questions are very difficult to answer without some more details about what seems to be happening, what symptoms are being seen, etc.

Resolving SQL Server data in different databases
Arthur Fuller
One all-too-common problem that data modelers and DBAs face is how to resolve SQL data in different databases. Arthur Fuller walks you through a sound approach for addressing this problem.

How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888
Bob Dorr, Sameer Tejani
SQL Server error messages 17883, 17884, 18887, and 17888 indicate a health state affecting worker scheduling that can have detrimental affects on the concurrency and throughput of Microsoft® SQL Server™. SQL Server error messages 17883 and 17884 were introduced in SQL Server 2000 Service Pack 3 and in SQL Server 7.0 Service Pack 4 (as error messages 17881 and 17882) to provide basic scheduler health monitoring. Error messages 17887 and 17888 are new health monitoring conditions that were added in SQL Server 2005.

Index usage stats in SQL Server 2005
Decipherinfosys
In one of the previous blog posts, we had covered the different types of dynamic management views that have been introduced with SQL Server 2005. One of those index related views is "sys.dm_db_index_usage_stats". Whenever an index is used in the system, a row is added to it if it doesn't already exist. If it exists, then the respective counter gets incremented. Every seek operation, scan operation, a look-up or an update due to a DML operation is counted and the respective counter gets incremented. Both application/end user submitted queries as well as internally generated queries (example: scans for collecting stats) increment different counters in the system. So, where does this come handy? Prior to rolling out your application(s) in production, you can conduct a thorough benchmark for your application flow and see which indexes are being used in your system i.e. which indexes are being heavily used, which are lightly used and which incur a maintenance overhead. So, if you find that there are indexes that are incurring heavy maintenance overhead and are very seldom used by queries, you might want to either drop them or re-design some of the indexes in the schema.

Writing to Word from SQL Server
Phil Factor
Having helped Robyn with her Excel Workbench, I couldn't get out of my head the idea of achieving the same effect with MS Word. After all, from the data viewpoint, MS Word documents are just a series of paragraphs and tables aren't they? Surely, it should be easy to read and write data between SQL Server and Word.

SQL Server Security Workbench Part 1
Robyn Page and Phil Factor
SQL Server Security is sometimes rather a blind spot to application developers. This has been widely illustrated by the success of very simple attacks on database-driven websites, which would have been prevented by even moderate security measures. In this workbench we will present two slightly different security models for a database. One of these uses roles to deny access to tables and views, and the other relies on withholding permissions to all objects except the stored procedures that make up the application-interfece. So what are these formas of attack on databases that have been so successful? Well, nothing very clever, just ways of testing whether any real security has been put in place. SQL Injection attacks are just one example of the many ways of gaining access to data with malicious intent.

Report Design: Best Practices and Guidelines
Brian Welcker and Chris Hayes
This paper consolidates general information, best practices, and tips for designing Microsoft® SQL Server™ Reporting Services reports. It is intended to provide a starting point for design questions and an overview of some of the capabilities of Reporting Services.

Reporting Services: Using XML and Web Service Data Sources
Jonathan Heide
This paper consolidates general information, best practices, and tips for designing Microsoft SQL Server Reporting Services (SSRS) reports. It is intended to provide a starting point for design questions and an overview of some of the capabilities of Reporting Services. (12 printed pages)

SQL Server 2005 Command Line Tool "SQLCMD" - Part III
Muthusamy Anantha Kumar
In Part I and part II we saw a few ways of using the SQL Server 2005 SQLCMD command line utility. In Part III of this series, I am going to illustrate how to use scripting variables, startup scripts and variable substitution.

Performance of Sun X4500 at JHU (Thumper.pha.jhu.edu) under Windows, NTFS and SQLserver 2005
Jim Gray
This is a balanced system with 16GB of fast memory, 4 fast processors, and enough disks and disk bandwidth to keep them busy when doing OLTP or data mining. Sun Microsystems loaned an X4500 to the Johns Hopkins University Physics Department in Baltimore, Maryland to do Windows-SQLserver performance experiments and to be a public resource for services like SkyServer.org, LifeUnderYourFeet.net, and CasJobs.sdss.org. The system's applications will soon be on the Internet, but for now we are just testing it. I termserved to Thumper.pha.jhu.edu from San Francisco. The X4500 is a quad-core 2.6GHz Opteron, with 16GB of RAM, 6 Marvell SATA controllers, 48 500GB Hitachi 7krpm SATAII disks, and dual Gbps Ethernet in a 4U package consuming about 0.8 KW when all the disks are working hard. As a first step we installed Windows 2003 Standard SP1 in 64-bit mode, Visual Studio 2005, and SQLserver 2005 Standard (64bit) SP1.

Selecting a SQL Server Recovery Model
Greg Robidoux
SQL Server offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.

Using Stored Procedures for Change Processing in the ADO.NET Entity Framework
Shyam Pather
Most people who've played with the ADO.NET Entity Framework eventually ask whether they can replace the SQL statements it generates with stored procedures of their own. This is important in many applications because direct table access is not allowed. Current builds of the Entity Framework support using stored procedures for inserting, updating, and deleting entities. We are actively working on supporting stored procedures for reading entities - this should be available in an upcoming build.

Some Time Intelligence Wizard Calculations are Inefficient
Chris Webb
Ahh, my old friend the Time Intelligence Wizard.... some of calculations it produces didn't work at all at RTM, some were still buggy in SP1 and now I see from the following threads on the MSDN Forum started by David Beavonn it seems to be generating inefficient MDX:

Can you solve Joe Celko's SQL puzzles?
Joe Celko
This collection of puzzles includes the original puzzles, so that the original readers can look up their favorites. But now many of them have new solutions, some use the older syntax, and some use the newer features. Many of the original solutions have been cooked by other people over the years. The term "cooked" is a puzzler's term for finding a better solution than the proposer of the problem presented. The original book contained 50 puzzles; this edition has 75 puzzles.

Gadfly zeroes in on Oracle bugs
Joris Evers
This is a great interview with David Litchfield. I was recently in a review with Bob Muglia, Senior VP for the Server and Tools business, where the SQL team presented there had yet to be a security GDR for SQL2K5. Bob asked if it was because the product is secure or if people just aren't looking for security issue. The team proudly answered: people are looking and they're not finding anything. I just don't understand why anyone still runs Oracle (behind a firewall or not). Editors' note: After this story was published, the reporter asked David Litchfield follow-up questions about ties to Microsoft and Oracle upon learning that Microsoft was a customer of NGS Software.

Network Port Configurations for MSDTC
Decipherinfosys
What is MSDTC? MSDTC is the Microsoft Distributed Transaction Coordinator, which is a transaction manager program that permits client applications to include several different sources of data into one transaction. MSDTC then coordinates committing the transaction across all of the servers that are listed in the transaction. MSDTC runs on all Windows operating systems, and is also installed by a variety of Microsoft applications, including Personal Web Server and SQL Server.

SSIS: OnPipelineRowsSent event
Jamie Thomson
An interesting discussion today on the SSIS Forum made me realise that perhaps not all SSIS developers are aware of the OnPipelineRowsSent event in SSIS and how valuable it can be, certainly when debugging data-flows. This isn't all that surprising because Books Online is very sparse in this area. Hence I thought it would make a useful blog topic.

The case of the missing Business Intelligence Studio
Kevindente
One of my co-workers was struggling with a strange problem recently - he installed SQL Server 2005 (with Analysis Services 2005), but the Business Intelligence Studio application didn't install. If you haven't seen it, BI Studio is really just a special version of the Visual Studio 2005 IDE, with Analysis Services-specific projects and editors. What he saw was that even though the SQL Server installer claimed to have installed it, and even created a shortcut for it, the actual devenv.exe executable wasn't installed (the shortcut pointed to an invalid path). I'd installed SQL Server many a time, and never seen this problem. A search of the MSDN forums turned up this helpful topic, with the following advice from 'softie Dan Jones:

Defragment Indexes stored procedure
Tara Kizer
This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels. If you plan to use DETAILED for @statsMode, you may want to consider running the query that builds the #FragIndex table on a database snapshot. Kalen Delaney mentions this in the May 2006 edition of SQL Server Magazine (InstantDoc ID 49769).

Sql Server memory 101
Saurabh Singhal
Recently a customer wanted to know some details on how SqlServer manges memory, sort of the basics. Presented below is some of the starter resources for learning about it, I would be happy to hear any specific questions/comments that anyone has after going thru the material below.

Learning Reporting Services
Chris Randall
This week, I've been presenting our 4-day SQL Server Reporting Services 2005 class. A hybrid drawn from several sets of materials, I'll use this post to list other resources that my attendees might benefit from using:

Joe Celko's Answers
Joe Celko
This article is excerpted from Joe Celko's SQL Puzzles and Answers, Second Edition , with permission of Morgan Kaufman Publishers. © 2007 Elsevier Inc. All rights reserved. For more information about this book, and other similar titles, please visit www.mkp.com.

Intent Insurance part II: Checks and balances
James Green
It is probabbly wise to remember domain rules in the database when considering Intent Insurance. A common scenario for a check constraint is to ensure the range of values in a field in cases where no foreign key constraint is available to constrain the values. The typical example is the case of a [State] field, where a check constraint on the table may look like:

Microsoft Meets Demands with SQL Server
Efem Nkanga
The Product Marketing Manager for Server and Tools products in Microsoft, West Africa., Mrs. Ejieke Maduka-Ezeadiugwu has stated that the SQL Server 2005 was introduced to help all segments of businesses meet ever-increasing customer demands by having greater business insight, resulting in higher efficiency and profitability.

How to Cluster SQL Server 2005
Brad M. McGehee
Believe it or not, the procedure to install a SQL Server 2005 instance onto a cluster is one of the easiest parts of getting your SQL Server 2005 cluster up and running. The SQL Server 2005 setup program is used for the install and does the hard work for you. All you have to do is make a few (but critically important) decisions, and then sit back and watch the installation take place. In fact, the setup program even goes to the trouble to verify that your nodes are all properly configured, and if not, will suggest how to fix most problems before the installation begins.

SQLCLR Scalar Functions
Brad Richards
User-defined scalar functions are likely the most obvious candidates for SQLCLR usage. There are two primary reasons for this. The first is that CLR functions actually have lower invocation overhead than T-SQL functions. T-SQL functions require the runtime to create a new T-SQL frame, which is expensive. CLR functions are embedded in the plan as a function pointer for direct execution.

Memory Model
Chris Brumme
One of the suggestions for a blog entry was the managed memory model. This is timely, because we've just been revising our overall approach to this confusing topic. For the most part, I write about product decisions that have already been made and shipped. In this note, I'm talking about future directions. Be skeptical.

Startup, Shutdown and related matters
Chris Brumme
Usually I write blog articles on topics that people request via email or comments on other blogs. Well, nobody has ever asked me to write anything about shutdown.

The Exception Model
Chris Brumme
I had hoped this article would be on changes to the next version of the CLR which allow it to be hosted inside SQL Server and other "challenging" environments. This is more generally interesting than you might think, because it creates an opportunity for other processes (i.e. your processes) to host the CLR with a similar level of integration and control. This includes control over memory usage, synchronization, threading (including fibers), extended security models, assembly storage, and more.

Apartments and Pumping in the CLR
Chris Brumme
I've already written the much-delayed blog on Hosting, but I can't post it yet because it mentions a couple of new Whidbey features, which weren't present in the PDC bits. Obviously Microsoft doesn't want to make product disclosures through my random blog articles.

Finalization
Chris Brumme
Earlier this week, I wrote an internal email explaining how Finalization works in V1 / V1.1, and how it has been changed for Whidbey. There's some information here that folks outside of Microsoft might be interested in.

Hosting
Chris Brumme
My prior three blogs were supposed to be on Hosting. Each time I got side tracked, first on Exceptions, then on Application Compatibility and finally on Finalization. I refuse to be side tracked this time… much.

Enhancements in SQL Server 2005 Profiler
Decipherinfosys
There are several important enhancements introduced in SQL Server 2005 version of profiler which make troubleshooting and performance tuning a lot simpler.

SSIS - Using a checksum to determine if a row has changed
Phil Brammer
It is one of the more common questions over on the SSIS Forums. How can I check if a record exists and if so, how can I check (quickly) if it has changed. Jamie Thomson has a blog post that outlines this, and I've made it a sticky post on the forums. However, if you need to check many, if not all of the columns in a table for changes, Jamie's blogged method might be a bit laborious. This is where the Konesan's Checksum transformation comes in. Read on…

Simple T-SQL Proper Case User-Defined Function
Jeff Smith
I posted this one a long time ago and needed to use it today, so I thought I'd post it up here as well in case anyone finds it useful. This simply attempts to capitalize the first letter of each word for the string passed in. Use it to help clean up some pre-existing data, but don't use it as a way of presenting your data that is stored improperly since it isn't exact.

The CLR commits the whole stack
Joe Duffy
The CLR commits the entire reserved stack for managed threads. This by default is 1MB per thread, though you can change the values with compiler settings, a PE file editor, or by changing the way you create threads. We've been having a fascinating internal discussion on the topic recently, and I've been surprised how many people were unaware that the CLR engages in this practice. I figure there's bound to be plenty of customers in the real world that are also unaware.

BULK INSERT - FIRSTROW and BATCHSIZE cause very poor performance and significant Writes to LDF and MDF
Tony Rogerson
Quick Tip about BULK INSERT and using FIRSTROW - the BATCHSIZE parameter makes performance suck and you'll never guess why! What do you think FIRSTROW does? According to Books Online it "Specifies the number of the first row to load.", so baring that in mind what does BATCHSIZE do? According to BOL it "Specifies the number of rows in a batch; each batch is copied to the server as one transaction."

How to create a corrupt database using BULK INSERT/ UPDATE and BCP - SQL Server as a HEX editor
Tony Rogerson
This entry shows you how to use a combination of BULK INSERT, UPDATE and BCP queryout to create a database corruption which you should then be using to test your database consistency checking processing in your environments.

Demo V: OfflineApplication - Oracle Backend
Rafik Robeal
One of the design goals of Sync Services framework is to enable heterogeneous database in the backend. That explains the open design of the DbServerSyncProvider class. If you haven't noticed already all the commands supplied to the provider and SyncAdapters are or type DbCommand which means you can pass any of ADO.NET command type (SqlCommand, OracleCommand, OdbcCommand, OleDbCommand and any vendor implementation too).

SQL Server 2005 Integration Services - Part 44 - Bulk Insert Task
Marcin Policht
In the recent installments of our series dedicated to SQL Server 2005 Integration Services, we have been discussing individual Control Flow tasks, focusing on their unique functionality and characteristics. One of the few remaining items in this category is the Bulk Insert task, which will be the topic of this article.

T-SQL Drawing
Roji. P. Thomas
Here is a humble attempt to draw a landscape using T-SQL !

Path Enumeration using Prime Number Products
Roji. P. Thomas
Managing Hierarchies in Relational Database Environment is always a challenging task. Over the years many models like Adjacency List Model, Nested Set Model, Nested Interval Model, Path Enumeration Model etc. were proposed and implemented. There are also other popular methods using recursion and user defined function. SQL Server 2005 T-SQL Extensions has special operators for managing recursive queries. Joe CELKO has written a book exclusively on Managing Trees and Hierarchies.

How do you get current Job Name?
Roji. P. Thomas
Today a colleague asked me this question. How do you get current Job Name from a job step? Well, I did'nt know the answer :( It should be simple. If we want to know the current procedure name from inside the procedure, we can use something like OBJECT_NAME(@@PROCID). Do we have something like @@JOBID? Well, NO. I turned to google and learned about SQL Agent Tokens. That was news to me.

Responsibilities of a Development DBA
Roji. P. Thomas
Recently, I was asked to define the responsibilities of a Development DBA. The following is the list of items that came to my mind. Feel free to add items you feel appropriate in the comments section.

Named Constraints on Temporary tables
Roji. P. Thomas
Last week I was investigating a peculiar issue reported by our testing team. They are receving an error like the following occassionally.

Difference between Table Variable and Temporary Table
Roji. P. Thomas
Table variables were introduced in SQL Server 2000. Many times they are presented as a replacement for temporary tables. There is a lot of arguement about the performance advantages of one over the other. It is generally believed that table variables give better performance than temporary tables. But there are so many cases reported where the usage of table variable slow down the performance, especially when the number of rows involved are high. So now the general recommendation is that use a table variable only if there is a performance gain over the temp table.

Common (TOP 10?) T-SQL Programming mistakes
Roji. P. Thomas
1. TOP Without ORDER BY: SQL Server doesn't guarantee the order of records returned, if an explicit ORDER BY clause is not specified. Records are not returned in the order they were created. Most of the times records are returned in the order of the clustered index, but not necessarily always.

Difference Between SET and SELECT
Roji. P. Thomas
T-SQL Programmers use both SET and SELECT Statements for variable assignments. Lets see what are the differences.

Object-Level Backups
Phil Factor
It was last year that I was forcefully reminded that regular backups are not always enough. I was working on the database of a live website. I had attached to it remotely across the internet via Query analyser, and was deleting a customer. It was after midnight at the end of a long working day, and I typed in..

Differences between COALESCE and ISNULL
Roji Thomas
I am trying to document the difference between some of the Keywords/functions, that are used for the same purpose and has some differences.

Let Us Count them!
Roji Thomas
Lets have a quick look on the differences between using COUNT(*) and COUNT(colname). Also discuss other ways to get the count of rows.

Fancy Scoping
Roji Thomas
Try the following query. As we know there is no column called au_id in the TITLES table, and the query returns all the records in the Authors table, instead of an invalid column name Error.

Using OpenXML
Raj Vasant
SQL Server 2000 added XML support awhile back, though it was limited in what is offered and can be a little confusing. SQL Server 2005 will add many more features and functions, but in the meantime, if you are looking to add some XML support to your 2000 server, new author Raj Vasant brings us some basics on using OpenXML.

Retrieving Data as XML from SQL Server
Mitchell Harper
All the hype that once surrounded XML is finally starting to die down, and developers are really beginning to harness the power and flexibility of the language. XML is a data descriptive language that uses a set of user-defined tags to describe data in a hierarchically-structured format.

NULL Versus NULL?
Michael Coles
Dealing with NULL data is something that often confuses new SQL Server developers, but even experienced DBAs might not understand all the intricacies of NULL operations. In a follow up to his highly acclaimed Four Rules of Null article, Michael Coles brings us a few new tricks with NULLs.

Maximum Row Size in SQL Server 2005
Andy Warren
What is the page size in SQL Server 2005? That's an easy question, but what is the maximum row size? They're not the same thing and ANdy Warren shows you why.

Using BACKUP and RESTORE in SQL Server -- Full Backups
graz
This article covers the basics of full backup backups and restores in SQL Server. The examples are from SQL Server 2005 however it applies to SQL Server 2000 and SQL Server 2005. This is a very basic article covering full database backups, database restores and the simple and full recovery models.

SQL Server Spotlight on Santeri Voutilainen
Steve Jones
The depths of the storage engine must be some interesting places and we dive a little into them with our look at Santeri Voutilainen, one of the very talented SQL Server engineers who worked on the lock manager.

Running a Query Using a Text File for Input
Roy Carlson
When automating administrative actions for SQL Server, there are a number of ways to handle the workflow. Longtime SQL Server DBA Roy Carlson brings us a technique for using a text file as input to the standard SQL Server tools.

Citrix MetaFrame, SQL Server, and the DBA
Brian Kelley
In this article, Brian looks at how Citrix MetaFrame can use SQL Server for its internal data repository. The basic operation of SQL Server in a Citrix MetaFrame installation is covered. In addition, Brian looks at how a DBA is an integral part of the support team for a successful Citrix MetaFrame farm.

Problems In Building a Data Warehouse
Janet Wong
Building a data warehouse usually isn't a small project, but somehow management sometimes sees it as something that can quickly eb done with a tool or two. Longtime DBA Janet Wong brings us a look at some of the problems you might face when getting ready to embark on this type of project.

Practical Methods: Naming Conventions
Michael Lato
Everyone should establish some sort of naming convention for their SQL Server platform. It helps to ensure that developers and DBAs can easily find objects and communicate with one another. New author Michael Lato brings us the start of a series on organizing your SQL Server code with an article on naming conventions.

Full Text Search Follies
Robert Pearl
Are you considering upgrading from SQL Server 2000 to SQL Server 2005? Are you using Full-Text Search? One of the top SQL Server consultants in New York walks us through an interesting issue that you might want to consider before migrating your databases.

Using SMS 2003 SQL Views to Create Custom Reports
Microsoft
Microsoft Systems Management Server (SMS) 2003 Reporting uses Microsoft SQL Server views to provide access to data from the tables in the SMS site SQL database and to offer an efficient reporting option. The SMS site SQL database contains a large collection of information about the network, computers, users, user groups, and many other components of the computing environment. This database also contains objects that represent SMS items such as advertisements, packages, queries, reports, and status messages.

Common Table Expressions in SQL Server 2005
Srinivas Sampath
The next evoution of T-SQL, which will be released in SQL Server 2005, contains a number of enhancements designed to allow you to write more powerful queries while keeping the code structured in a way that makes development and understanding it easier. Coming ever so closer to the SQL-99 specification with Common Table Expressions, new author Srinivas Sampath brings us an introduction to this new way of writing complex queries.

T-SQL UrlEncode
Peter DeBetta
I was perusing the newsgroups when I came across a request in the microsoft.public.sqlserver.clr newsgroup to see if there was a way to use System.Web.HttpUtility.UrlEncode in T-SQL. I know using the CLR is an option, but I decided to write a T-SQL equivalent. I reflected the code to figure out the exact rules for UrlEncoding. Then, I discovered much to my delight, a new system function in SQL Server 2005 that converts a string to its hexadecimal equivalent as a string- sys.fn_varbintohexstr. I knew there was an extended stored procedure in prior versions of SQL Server to do this conversion, but this function made writing this UrlEncode function even easier.

T-SQL UrlDecode
Peter DeBetta
While I'm at it, here is the counterpart to the UrlEncode function - UrlDecode:

T-SQL Convert Hex String to Varbinary
Peter DeBetta
One last post before I go on vacation next week... SQL Server 2005 includes an undocumented function, sys.fn_varbintohexstr, that converts a hex value to a string representation of that hex value (0x3a becomes '0x3a'), but there is no function to go back from a hex string to a hex value. Now there is ::

[В начало]

ФОРУМ SQL.RU

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

База SQL не аттачиться! ошибка 823!!! помогите плииззз.
Очень срочно и важно
Посылаемые изменения в таблицу и старые данные
Не боян еще? SQL 2005 SP2 выложили
Число записей с n1 + n2 + n3 + n4 = 0
timeout при выполнении UPDATE одной записи в небольшой таблице небольшой базы данных
Переезд на Win x64
function
Как быстро определить сколько лет человеку по дате рождения
dts
MS SQL Server 2005 + Oracle SQL Developer
Клиенты начали терять параметры хранимок
Шифрования трафика клиент сервер
возврат значений из функции
Проблемы с SqlServerAgent
MSDTC через VPN
Срочно!!!(Ж),SQL Server 2000,статус БД(emergency mode) -- как лечить???
Процедура в процедуре
Право использования системных процедур
syscomments - compressed (сжатая процедура)

[В начало]

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

Linked server
MSSQL 2005 Query Notification - bug? By design?
CDATA и название элемента
DROP ENDPOINT
Поздравляю!!!
FileSystem or MSDB
open DTS error
Где скачать папку с сценариями
Data Pump Task. Transformations
DTS redaktor SQL 2000 formata DTS'ams
русский(ruslr.dll) для "full-text search" - где достать ?
Как сделать чтоб SQL Managment Studio увидела SourceControl PlugIn
Репликация процедур

[В начало]

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