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

СОДЕРЖАНИЕ

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

Привязка ввода-вывода и резервирование снимком

Привязка ввода-вывода (affinity I/O mask) выполняется специальными, скрытыми планировщиками, и управляет на уровне ядра такими задачами ввода-вывода буферного пула, как журналирование (log writer).

Примечание переводчика: привязка ввода-вывода для SQL Server 2005 SP1 действовала только на внутренний процесс отложенной записи.

Новая опция глобальной конфигурации - "привязка ввода-вывода" была добавлена в SQL Server 2000 SP1. Статья Microsoft Knowledge Base: INF: Understanding How to Set the SQL Server I/O Affinity Option описывает, как настроить привязку ввода-вывода.

Поставщики решений могут использовать так называемые виртуальные устройства (Virtual Device -VDI), на основе которых выполняется резервное копирование методом создания снимка, причем достигается практически зеркалирование данных. Для того чтобы SQL Server мог работать с таким снимком, необходимо гарантировать стабильность данных по меткам времени и исключить возможность оборванной записи. Это достигается путём фиксации всех новых операций ввода-вывода и завершая всех запросов ввода-вывода на уровне базы данных. Резервное копирование посредством Windows Volume Shadow Copy Service (VSS) является одним из основанных на VDI приложений.

Дизайн SQL Server 2000 не позволял контролировать фиксацию ввода вывода (frozen I/O) в комбинации с привязкой ввода-вывода. Резервирование снимком одной базы данных фиксировало запросы ввода-вывода (чтение и запись) для всех баз данных, что делало нежелательным одновременное выполнение привязки ввода-вывода и резервирования снимком.
Как было показано в указанной чуть выше статье, SQL Server 2005 поддерживает привязку ввода-вывода. Это позволяет исправить ситуацию, при которой возможно было зафиксировать все запросы ввода-вывода при настроенной привязке ввода-вывода.
Привязка ввода-вывода в SQL Server нужна только в очень редких случаях, и рекомендована в качестве настраиваемой опции глобальной конфигурации только для высокопроизводительных серверов. Использовать её можно только после тщательного тестирования, и если после тестирования будет явно видно, что включение привязки ввода-вывода приведёт к заметным улучшениям в работе сервера. Успешное применение привязки ввода-вывода может увеличить общую производительность экземпляра сервера. Без внесения указанных в статье изменений в глобальную конфигурацию, планировщики привязки ввода-вывода стараются поддерживать использование своего процессора на достаточно не высоком уровне и стараются не мешать другим планировщикам использовать не задействованные ресурсы этого процессора.

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

[В начало]

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

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

Кто властвует на рынке BI?
olap.ru
Затраты на средства бизнес-анализа (Business Intelligence, BI) в опросах ИТ-директоров стоят на первом-втором местах. А эффективность этих затрат в значительной степени зависит от совершенства используемых средств BI. Если раньше эта задача решалась главным образом благодаря применению отдельных, не связанных между собой инструментов бизнес-анализа, то в последние годы на первый план вышли аналитические платформы - наборы интегрированных между собой инструментов (компонент) бизнес-анализа. Вниманию читателей предлагается взгляд экспертов на представленные на рынке аналитические платформы.

Руководство по безопасности Windows Server 2003
Microsoft
Руководство по безопасности Windows Server 2003 содержит конкретные рекомендации по укреплению безопасности компьютеров с Microsoft® Windows Server™ 2003 с пакетом обновления 1 (SP1) в трех различных корпоративных средах. В одной из сред должны поддерживаться старые операционные системы, такие как Windows NT® 4.0 и Windows® 98. В другой используется Windows 2000 и более новые операционные системы. Наконец, в третьей безопасности придается настолько большое значение, что значительное снижение функциональности и усложнение работы с клиентскими компьютерами считается допустимым для достижения максимального уровня безопасности. Эти три среды называются средой устаревших клиентов (LC), средой корпоративных клиентов (EC) и специальной безопасной средой с ограниченной функциональностью (SSLF) соответственно.

Анализатор соответствия рекомендациям, многоядерные процессоры и многое другое
Nancy Michell
Вопрос. У меня есть несколько вопросов относительно правила анализатора соответствия рекомендациям (BPA), касающегося применения Schema Qualified Tables/Views (Квалифицированные схемой таблицы и представления). Согласно документации BPA, это правило не проверяет временные таблицы на квалификацию схемой. Ссылки флагов отчетов BPA на временные таблицы созданы в хранимых процедурах. Требуется ли их квалифицировать? Если да, то какой схемой? Казалось бы, временные таблицы должны быть квалифицированы для их владельцев, так же, как и другие таблицы.

AdventureWorks Light
Ирина Наумова
В феврале Microsoft включила в состав поставляемых к SQL Server 2005 примеров: SQL Server 2005 Samples and Sample Databases (February 2007) облегчённую версию учебной базы данных AdventureWorksLT.msi, размер которой в дистрибутиве составляет 2251КБ.

Использование веб-сервисов для работы с удаленными SQL серверами (исходники)
Tomas Shestakov
В данной статье рассматриваются проблемы, возникающие при работе с SQL серверами, находящимися в сети Интернет, а также пути их разрешения при помощи веб сервисов. В качестве примера приводится организация работы с MS SQL Server 2000 посредством использования веб службы WebDBConnector, разработанной автором этой статьи, скомпилированный код которой доступен на сайте www.megaprog.4u.ru.

Репликация слиянием на практике
Гарри Зайка
Создаем высоконадежное и устойчивое к сбоям решение, которое легко настраивать и поддерживать. Я работаю старшим консультантом в группе Financial Services Group подразделения Microsoft Consulting Services. Моя задача - помогать клиентам, работающим в финансовой сфере, эффективно использовать технологии баз данных Microsoft. Недавно одному заказчику потребовалось высоконадежное и восстанавливаемое после сбоя решение. Администратор баз данных в его компании не имел практического опыта поддержки решений с высоким коэффициентом надежности (включая репликацию), поэтому клиент хотел получить решение, которое будет несложно настраивать и поддерживать. Я расскажу об этом случае и перечислю шаги, выполненные мной при разработке решения, которое не требует глубоких знаний в области написания сценариев. Если требуется простое в использовании решение с хорошей производительностью и возможностью восстановления после сбоя, изучите этот пример. Возможно, он поможет понять, является ли репликация слиянием правильным выбором в той или иной ситуации.

Десять непреложных законов безопасности
Майкрософт
В центре обеспечения безопасности корпорации Майкрософт ежегодно анализируются тысячи отчетов о нарушении безопасности. Иногда оказывается, что в отчете действительно описывается уязвимость системы безопасности, возникшая как следствие изъяна в одном из продуктов корпорации Майкрософт. В этих случаях специалисты корпорации Майкрософт как можно скорее разрабатывают исправление, устраняющее эту ошибку (см. раздел A Tour of the Microsoft Security Response Center ["Общие сведения о центре обеспечения безопасности корпорации Майкрософт"]). Бывает также, что описываемая проблема является следствием ошибки при использовании продукта. Однако многие отчеты не попадают ни в одну из этих категорий - хотя в них описываются настоящие проблемы с системой безопасности, однако эти проблемы не являются следствием изъянов в продуктах. За годы работы сотрудники центра создали список подобных проблем, назвав его "Десять непреложных законов безопасности".

Начато открытое бета-тестирование Longhorn
Служба новостей IDG
Microsoft выпустила первую общедоступную бета-версию операционной системы Windows Server Longhorn. Это третья бета-версия за все время тестирования ОС и последняя перед выпуском финальной, планируемым на вторую половину года. Особенностью Longhorn beta 3 стало наличие встроенного варианта командно-сценарной оболочки PowerShell для автоматизации административных задач, которая ранее предлагалась только в качестве отдельного дополнения. Кроме того, впервые можно будет ознакомиться с неотключаемым межсетевым экраном в Server Manager и особым вариантом инсталляции Server Core, позволяющим установить минимум необходимых компонентов ОС для девяти из 18 поддерживаемых серверных ролей и автоматически сконфигурировать систему в расчете на обеспечение максимальной надежности работы.

Новый блог участника Russian SQL Server Club
Александр Гладченко
Вчера появилось первое сообщение в техническом блоге Ирины Наумовой, которая совсем недавно была отмечена Microsoft статусом SQL Server MVP. Ирина работает в Ростелекоме и занимается там администрированием баз данных SQL Server. В своём первом сообщении Ирина обращает внимание на то, что теперь в поставке примеров к SQL Server 2005 есть компактная учебная база данных AdventureWorksLT, которую зачастую удобнее использовать, чем её полный аналог.

Важная новость! Стала доступна для загрузки Windows Server Code Name "Longhorn" Beta 3
Anton Belousov
У меня сегодня просто день новостей:) Наконец-то, появилось то, о чем все долго говорили и меня спрашивали. Стала доступна для загрузки Windows Server Code Name "Longhorn" Beta 3 !!!!! Наша новейшая серверная операционная система. Скачать можно со страницы http://www.microsoft.com/technet/prodtechnol/beta/lhs/default.mspx.

20 апреля 2007 года на сайте загрузки Microsoft стал доступен BOL для Orcas и Katmai
Александр Гладченко
Microsoft SQL Server 2005 Compact Edition - компактная база данных для внедрения в мобильные и настольные приложения. Управление базой данных SQL Server Compact Edition на настольном компьютере или на мобильном устройстве осуществляется с помощью SQL Server Management Studio или SQL Server Management Studio Express. SQL Server Compact Edition (SSCE) будет поддерживаться и получит дальнейшее развитие в Microsoft Visual Studio ® Code Name "Orcas", а средства администрирования базы данных будут совместимы с Microsoft SQL Server Code Name "Katmai". "Orcas" - это следующая версия после Microsoft Visual Studio 2005, а "Katmai" является следующей версией, после Microsoft SQL Server 2005.

Упрощенное администрирование групповой политики в Windows PowerShell
Thorbjцrn Sjцvold
Технология групповой политики Microsoft не была принята моментально - вначале ее было сложно понять, и требовалось разобраться в службе каталогов Active Directory - странной новой службе, совершенно не похожей на домены Учетных записей/Ресурсов, являвшиеся стандартами в то время. Сегодня групповая политика - это основа управления практически каждой организацией с инфраструктурой Windows®. Мне кажется, то же самое произойдет и с Windows PowerShell™, самой новой технологией управления от Майкрософт. На самом деле Windows PowerShell наверняка значительно упростит вашу работу в качестве администратора групповой политики.

[В начало]

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

Reliable Repositories: Using Microsoft Forefront Security for SharePoint to Defend Collaboration
Adam Buenz
As the techniques and technology that organizations use to construct business information warehouses evolve, malware attack methods similarly improve and advance. When utilizing a Web application, such as Microsoft Office SharePoint Server 2007 (MOSS), to facilitate such a core data repository while fostering a collaboration platform to unite virtual teams, the first thing that comes to mind when analyzing information warehouse security is the Web layer. However, there are numerous planes of security that must be inspected when assessing the inclusive threat background. Employing MOSS as an information warehouse component of an Enterprise Content Management solution exposes the possibility for content-based and malware attacks, greatly expanding the inventory of effective threats. Microsoft Forefront Security for SharePoint (FSSP) provides the essential mechanisms to close this gap. It secures MOSS-based storage mediums so that vital business data can be protected from possible malware that could otherwise compromise repositories. By doing this, it mitigates risks such as revenue loss, operations disruption, and industry embarrassment.

SSIS Package to generate SSAS 2005 aggregates
Vidas Matelis
I prefer to use usage statistics to generate SSAS 2005 aggregates, but during the initial phases of a project they are not available. So I usually build a set of random aggregates, and after enough usage statistics are accumulated, I rebuild aggregates based on usage. Microsoft provides a nice wizard to generate aggregates on measure groups and partitions. But because I have to do this task way to many times, I decided to build a simple SSIS package that does it for me.

Vardecimal storage usage in SQL Server 2005
Vidas Matelis
With SQL Server 2005 Service Pack 2 Microsoft introduced storage format vardecimal. As I work a lot with big fact tables, I decided to test how this new format affects storage. If you want to get estimated impact on using vardecimal storage format, you can run stored procedure sp_estimated_rowsize_reduction_for_vardecimal. This SP expects table name as parameter and returns row count, average row length for fixed format and average row length for vardecimal format.

SSIS package to process all dimensions in SSAS 2005 DB
Vidas Matelis
It is quite often that I have to process all the dimensions in a database. I like to use scripts for that. Here I have step by step guide on how to create an SSIS package that processes all dimensions in one database.

SSIS package to process all dimensions in SSAS 2005 DB (continuing)
Vidas Matelis
After I published blog entry about a SSIS package that processes dimensions, I received a suggestion from Darren Gosbell (his blog is here) that instead of building XMLA code by concatenating strings, I should use the CaptureXML option from an SSAS server object and then process dimensions using dim.Process method and execute XMLA using ExecuteCaptureLog routine.

Numbers scale impact on SSAS 2005 storage
Vidas Matelis
Years ago, while I was using Analysis Services 2000, I had a strange problem. I migrated one of the cubes to be loaded from a SQL Server View instead of the actual table, and I noticed that the cube size almost doubled. It took me some time to find what was causing this. My original table had a few dozens fields with type decimal(19, 2). They were loaded into analysis services database with type double. After I migrated my fact table into this view, I also migrated some calculations. So I was multiplying my fields of decimal(19,2) type be another rate field with a decimal(9,8) type. As my rate field was valued between 0 and 1, I thought that this multiplication would not increase the size of the original field. That was a wrong assumption, as I significantly increased scale of the numbers. Instead of loading into cube values like 123.45, I was loading values 123.450123456789. All these extra numbers where not significant, but they were using a lot of space in the analysis services database. I quickly fixed my problem by converting calculated value to field with a size I was interested in: CONVERT(decimal(19, 2), Field * Rate) AS Field.

SSIS package that process all partitions/measure groups/cubes in one database
Vidas Matelis
Recently I posted a blog entry on how to process all dimensions in a single Analysis Services 2005 database using SQL Server Integration Services (SSIS) package. Here I'll add code to this package that will allow you to process whole database, cubes, measure groups or partitions.

SSIS Package to drop/create partitions based on partition list in the SQL Server table
Vidas Matelis
In my past logs I showed how to use SSIS package to process dimensions, cubes or build aggregates. I am slowly rebuilding my existing packages thanks to points from Darren Gosbell and Jess Orosz. . I found that using CaptureXML method is more convenient for me and it is definitely faster.

Using UserName to Control Data Access and Default Member in SSAS 2K5
Carrie Williams
How to use UserName to limit access to data in SSAS and set the Default Member. We will modify the Adventure Works SSAS cube to demonstrate the use of UserName.

SSAS security - different methods
Vidas Matelis
Last week I answered question in MSDN Analysis Services forum thread that at first appeared to be very simple. Somebody asked how do you control access to SSAS cubes if information about users and groups is inside SQL Server table and not in the Active Directory. I answered that you cannot do that. This was my understanding from reading Books Online and various articles. I was sure that Microsoft SQL Server Analysis Services 2005 works just with integrated security. For clients that do not use Active Directory you have an option to setup OLAP HTTP access and then control security to the folder where http dlls are. This does work, but you compromise security, as all people have the same access rights to database. So you have to implement additional security methods in the front end and in the firewalls (example access to http folder is allowed just for the specific front end, etc).

Getting Optimal Performance with Integration Services Lookups
Runying Mao and Len Wyatt
Most users see good performance from SSIS packages using out-of-the-box configurations and with little tuning. Sometimes, though, it is necessary to do tuning to get optimal performance. One of the most commonly used transformations is the Lookup transformation. There are several techniques for getting optimal performance from a Lookup transform in SSIS. A few of these are surprising the first time you see them. This article discusses some tuning techniques that we have found can be important in different situations.

Limit a sql script to only run with SQLCMD turned on
Simon Sabin
I really like SQLCMDso much so that most of my deployment scripts use SQLCMD. Its got some great features such as

Reusing Conversations
Remus Rusanu
One of the most common deployed patterns of using Service Broker is what I would call 'data push', when Service Broker conversations are used to send data one way only (from initiator to target). In this pattern the target never sends any message back to the initiator. Common applications for this pattern are:

Creating a Custom Content Formatter for SSNS 2005
Joe Webb
Microsoft SQL Server Notification Services 2005 (SSNS 2005) provides a foundation for developing and hosting notification applications. Once the technology's terminology, architecture, and core components are adequately understood, it's possible to build robust and scalable notification applications in relatively short order.

The Service Broker Alphabet Part 1
Simon Sabin
SQL Server MVP has been working extensively with SQL Server 2005 and one of the less well known features: Service Broker. In this short article, we get an introduction to some of the terminology you'll need to know to work with this subsystem.

The Service Broker Alphabet Part 2
Simon Sabin
SQL Server MVP has been working extensively with SQL Server 2005 and one of the less well known features: Service Broker. In this short article, we continue definitions of the terminology you'll need to know to work with this subsystem.

Dynamic Images and Databases
Dino Esposito
Should you store dynamically generated web-site graphics in a database or is the file system the better option? Dino illustrates how to make this decision in ASP.NET

SQL Server 2005 Maintenance Mayhem
Robert Pearl
Service Pack 2 for SQL Server 2005 has had a few issues and one of the big ones is maintenance plans. Longtime DBA and developer Robert Pearl, of Pearl Knowledge Solutions brings us a fix for your maintenance plans.

The Truth Table
Yousef Ekhtiari
Truth tables are an important part of working with logical values in SQL Server. Yousef Ekhtiari brings us some T-SQL that can help you construct those many variable truth tables and ensure you are getting the results you expect.

Auditing with SQL Profiler
Brian Kelley
SQL Server includes a great auditing tool: Profiler. It's not the easiest tool to use, however, and it's one that takes some getting used to. Our resident security export, Brian Kelley looks at a simple example of using this tool to audit logins.

Dynamic Management Views and Functions in SQL Server 2005
S. Srivathsani
DBAs have always been warned against using system tables in their code, but often there has been no other way to get information about the server. In SQL Server 2004, Dynamic Management Views have been provided that give you insight into almost every aspect of SQL Server. S. Srivathsani brings us a look at some of the DMVs and functions you can use.

A Refresher on Joins
Jambu Krishnamurthy
Knowing the various types of joins can greatly expand your T-SQL skills. Jambu Krishnamurthy brings us a refresher article on the various types of joins and how to write them.

Custom Auto-Generated Sequences with SQL Server
Jeff Smith
This article by Jeff Smith covers different ways to create sequence numbers. It starts with the basic identity and GUIDs and quickly moves to much more complicated sequence numbers including those with auto-incrementing mixed letters and numbers such as "A0001", etc. Jeff shows one of the easiest ways I've ever seen to efficiently handle very odd sequence numbers.

You Should Never Use IN In SQL To JOIN With Another Table
Denis
Do you use the following syntax?

SQL Server 2005 - Ad hoc updates to system catalogs are "allowed"
sqlmaster
Updates to system catalogs are not allowed - a big subject and nothing but opening door to trouble by allowing direct updates or modifications to system tables in SQL Server 2005. If I remember correctly since the days of SQL 6.5 version Microsoft has been discouraging udpates to system tables even though the provision has been enabled. Since SQL Server 2005 version it has been totally removed, which is a good thing for DBAs (especially).

Making the Most Out of the SQL Server 2005 Performance Dashboard
Brad M. McGehee
If you have been using SQL Server 2005 for a while now, you may wonder what I am talking about when I refer to the SQL Server 2005 Performance Dashboard. No, you are not suffering from overwork and fatigue, causing you to lose your mind and forget what new features have been included with SQL Server 2005. The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005 that became available shortly after the release of Service Pack 2 for SQL Server 2005.

Validate Your Refactored SQL Queries with a New SQL Server 2005 Operator
Chris Goddard
Prove that your refactored SQL query returns the expected results using the EXCEPT operator, new in SQL Server 2005. As a developer, how often have you been asked to refactor a SQL statement to increase performance, accommodate changes to database design, support a new database, or for a whole host of other reasons? Often you need to do the refactoring without affecting the results. When you refactor a query that returns the correct result set, adhering to the Hippocratic principle of "First, do no harm" will ensure you maintain quality. In this case, the principle as applied is "Make sure you return the same results."

New DMX Syntax option in SQL Server SP2
Jamie MacLennan
Quite a while back I promised to tell everyone about the subtle new feature in DMX in SQL Server 2005 SP2. Well, that day has finally arrived, true believers - your patience has paid off! :

Read Committed Isolation Level
Craig Freedman
SQL Server 2000 supports four different isolation levels: read uncommitted (or nolock), read committed, repeatable read, and serializable. SQL Server 2005 adds two new isolation levels: read committed snapshot and snapshot. These isolation levels determine what locks SQL Server takes when accessing data and, therefore, by extension they determine the level of concurrency and consistency that statements and transactions experience. All of these isolation levels are described in Books Online.

Sync Services: When should you author your own table schema for your offline clients?
Rafik Robeal
The question of creating the schema on the client store always pops up in almost any sync related conversation. Flexibility is desired but simplicity is a must! I can understand that and I believe that the sync framework has a good balance between both (feel free to tell me otherwise)…

Analysis Services 2005 in SQL Server has improved security
Baya Pavliashvili
Microsoft Analysis Services (MSAS) relies on Windows accounts for granting access to cube data as well as for administrative tasks such as processing cubes, altering server-wide configuration settings and modifying dimensional objects.

SSMS usage tips
Sqlmaster
Few tips I would share with the community about usage of SSMS tool within your environment, you can get acquaintance with SSMS quickly by using these features:

Full Control Over a Randomly Generated Password
Peter Larsson
All of us need a steady stream of random passwords for the various systems we manage. Peter Larsson brings us an interesting T-SQL solution that generates one for us.

Estimated rowcount affects the performance - SQL 2005
sqlmaster
Using Query Analyzer you can get an estimated execution plan for a query against a database, in that plan the estimated number of rows is incredibly important when Query Analyzer is choosing an execution plan. In the real terms this is something to be considered as it will cause performance problems. As you may be aware SQL Server uses a cost-based optimizer that evaluates the expected query cost and then chooses the plan that has the lowest expected cost. SQL Server never knows what the actual query cost is until it runs the query, so it's always possible that SQL Server will choose a more expensive plan.

Things to take care for performance tuning after upgrading from SQL 2000 to 2005?
sqlmaster
The first thing that needs to be touched on is whether the upgrade is going to be performed by taking an existing SQL Server installation and upgrading it or by installing SQL Server 2005 on a new machine and migrating databases to it. Also use the Upgrade Advisor tool that will determine the things to take care during the upgrade, I have seen many out there simply choose the BACKUP and RESTORE path which sometimes may not have a good deal of optimization without taking the upgrade path. References about Upgrade Advisor usability from here.

Execution plans caching and how SQL optimizer reuses for better performance
sqlmaster
Memory plays important role in Performance on a windows based platform, SQL Server 2005 features has a pool of memory that is used to store both execution plans and data buffers, only the part of memory is used to store execution plans that we call as 'procedure cache'. Query plan & Execution context are key ingredients for performance and there will never be more than 2 (max) copies of query plan in memory; 1 copy uses serial executions and another is for parallel executions. The parallel execution uses the degree of parallelism - dependant on the CPU. Execution context will have a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context.

Faster Way to Get Count of Rows in a Table
Glenn Berry
The most common way that I see people get a count of rows in a table in SQL Server 2005 is SELECT COUNT(*) FROM dbo.TableName WITH (NOLOCK). This works, but is expensive from a logical IO perspective. One alternative is to query sys.partitions to get the same information. Below are results for the two methods on a table with 43 million rows of data:

One Way to Convert from UTC Time to Local Time
Glenn Berry
I had a question from someone about how to convert from UTCTime to local time in T-SQL code. Here is one way to do it

RAID - performance key factor
sqlmaster
Both hardware RAID 1 (mirroring or duplexing) or RAID 5 (striping with parity) offer good data redundancy should a single hard disk in a RAID array fail. And as you might expect, there is some differences in performance between the two

Stress testing for application - tuning exercise
sqlmaster
Recently I have had a job to generate a test on a disk subsystem to test a capacity growth on one of our SQL Server system. I was referring through this LessonsLearned article by Geert Vanhoe to simulate the stress testing, I'm aware about using SQLIOSTRESS utility using the patternsinclude heavy page insert/split simulations, inserts, updates, checkpoint stress scenarios, read ahead, sorts, hashes, and backup scan activities, including large and varied scatter and gather I/O requests. The simulation imposes heavy data file activity that requires high transaction log activity as well

[SQL] Using 'GO 100' to execute a batch 100 times
Jon Galloway
The GO statement is used by SQL Server as a batch terminator. It's recognized by by tools which run scripts like SSMS, SMO, and SQLCMD, but it's not technically T-SQL. SQL Server 2005 added a new little trick to the GO command which lets you specify a number of times to execute the batch: GO 10

SQL Puzzle #2 - Paging through a table with GO 10
Jon Galloway
I just wrote about using GO 10 to execute a T-SQL batch 10 times in SQL Server Management Studio. Could you use it to page through data?

High CPU spikes affecting performance
sqlmaster
I have seen a common question across the forums & newsgroups that CPU is 100% when we are running a query on SQL Server. So it is important to understand whether CPU is affecting or is it a bad query that is affecting SQL Server performance in spite of high-spec hardware. I always recommend the article from SQLJunkies site about common symptoms and tools that clears out how to proceed further in order to assess the performance by collecting few benchmarking statistics using PERFMON (SYSMON) and PROFILER. This is applicable for SQL Server 2000 and 2005 versions, but the following is specific for SQL 2005 version only.

SSIS: Storing passwords
Jamie Thomson
Many questions on the SSIS forum relate to security aspects of using SSIS and a large percentage of those relate to the storing of passwords in configurations

Scripting the Installation of SQL Server 2005
Gregory A. Larsen
Having a consistent SQL Server environment is important. Knowing that all your SQL Server machines are setup the same helps to minimizing confusion when managing multiple machines. If you have lots of machines, it is absolutely imperative that all your SQL Server setups be the same. In order to make sure all of your SQL Server 2005 machines are the same you need to develop a method where you can script your installations. This article will discuss one method of scripting your installation to ensure all your machines are set up the same.

Problems when removing a filegroup
Roman Rehak
This week we ran into a weird problem. We were not able to remove an empty filegroup, SQL Server was throwing the 5042 error "The filegroup 'FG18' cannot be removed because it is not empty". There were definitely no files in that filegroup so the whole thing appeared to be more like a bug. As it turns out, this can happen if a table that was using the filegroup had statistics defined on that filegroup. You can tell if you have any dangling statistics for that filegroup if you run this query and replace X with your filegroup ID:

Monitoring server disk space in SQL Server
Hilary Cotter
DBAs must monitor their SQL Servers carefully to ensure that the system and user databases have sufficient disk space for the life time of the applications using these SQL Servers. Failure to do so can result in the following problems:

SQL Server Grouping Workbench
Robyn Page and Phil Factor
This Workbench is about using the GROUP BY clause in a SQL Statement. It is designed so it can be pasted straight into SSMS or the Query Analyser, though to run the examples, you'll need the data file too. Just click the "CODE DOWNLOAD" link at the top of this article. Also included in the download bundle is the original SQL file, if you need it.

Get tables, indexes file group information for a SQL 2005 databases
Sqlmaster
Using SSMS tool you can get information on tables, indexes and filegroup information for a database, how about using a TSQL statement:

SSIS - Transfer SQL Server Objects Debugge
Stephen LaPlante
A new SSIS developer finds a problem with the Transfer SQL Server Objects task. Join new author Stephen LaPlante as he digs through and discovers a bug in this task.

Maintaining a Log of Database Changes - Part 1
Scott Mitchell
One of the benefits of using Source Code Control (SCC) software like SourceSafe, Perforce, Subversion, Vault, and others is that the software keeps a detailed history of all changes to the source code. With SCC, you can see how the code for a particular file has changed over time, and when and who made the changes. Moreover, with such a log in place it is easy to rollback the system to a previous state. A log that tracks changes to a system is sometimes referred to as an audit log, as it provides a repository of information that can be used to audit a system. In the case of SCC, your boss might want to perform an audit of the system to determine how a particular bug was introduced. By examining this log, your boss could determine who entered the buggy line of code and when, as well look at what the code looked like before the bug was introduced.

What's the difference between database version and database compatibility level?
Paul Randal
I had a question this week from someone who'd heard me say at SQL Connections (paraphrasing) "database compatibility level is mostly about query parsing" and was having trouble trying to forcibly attach a 2005 or 7.0 database to a 2000 server.

SQL Server 2005 Database Mirroring
Glenn Berry
Database mirroring is a new high availability feature that was added to SQL Server 2005 (and enabled by default in SP1). It works at the database level rather than the instance level (like fail-over clustering). It requires SQL Server 2005 Standard Edition or Enterprise Edition (except for the Witness server, which can be Express Edition).

SQL Server Connectivity
Microsoft Team
SQL Server 2000 supports several methods of communication between client applications and the server. When the application is on the same computer as an instance of SQL Server 2000, Windows Interprocess Communication (IPC) components, such as Local Named Pipes or Shared Memory, are used. When the application is on a separate client, a network IPC, such as TCP/IP or Named Pipes, is used to communicate with SQL Server.

Katmai, Orcas Link Could Delay Longhorn Release
Barbara Darrow
Microsoft may synchronize its "Longhorn" Windows Server release with its promised SQL Server and Visual Studio updates, even if that means delaying Longhorn's availability slightly, CRN has learned.

CardSpace, SqlMembershipProvider, and More
Keith Brown
In this month's column, I'd like to take time to answer a few questions that I frequently get from readers. I'll cover information cards, the SqlMembershipProvider in ASP.NET 2.0, and access control GUIs.

Next-Generation Data Access: Making the Conceptual Level Real
Jos? Blakeley, David Campbell, Jim Gray, S. Muralidhar, Anil Nori
Eliminate the impedance mismatch for both applications and data services like reporting, analysis, and replication offered as part of the SQL Server product by raising the level of abstraction from the logical (relational) level to the conceptual (entity) level. (31 printed pages)

Unable to continue SSIS package after SP2
sqlmaster
From time to time we manage our SSIS packages by modifying the configuration file to allow the execution even though the service is stopped. 2 of the packages were working perfectly until we have applied the Service Pack 2 for SQL 2005 couple of weeks ago.

Putting cost limits on bad Queries
Decipherinfosys
SQL Server has a query governor cost option that can be applied to throttle bad/runaway queries in the application. This option (QUERY_GOVERNOR_COST_LIMIT) is present in SQL Server and can be changed either at the server level or at the connection level. If you are going to change it at the server level, you have to use the sp_configure command and if you want to change it at the connection level, you can use the SET command for changing the setting for that connection only. What this option enables does is that it applies a limit (in seconds) to the time duration for which a query can run. Once this setting is in place, the query engine will not allow the execution of any queries that have an "estimated" (not runtime) cost (in seconds) that exceeds that value. This setting takes place at execute or run time and not at the parse time.

Remove duplicates using T-SQL Rank() function and SSIS conditional split
Rafael Salas
Having to de-duplicate rows from source tables is a very common task in the data integration world and solutions range from the simple SELECT DISTINCT to the most complex algorithms. In the next few lines, I am going to share a technique I often use when dealing with duplicates in the source. First at all, some assumptions about the requirments and the ETL environment. Let's assume that the requirements dictate we need to pick the 'right' row and redirect the 'duplicate' rows to an error table that will support data quality reports. No matter where the source data is stored, as a part of the ETL workflow, we will stage source data in a SQL Server 2005 table

SYSK 338: What SQL Queries Are Currently Running?
Irena Kennedy
That's easy -- just run the SQL statement below…

PerformancePoint CTP3 - Possible release date and some future features in Monitoring and Analytics
David Francis
I was asked last night whilst presenting Monitoring and Analytics in CTP2 to the UK SQL BI users group (www.sqlserverfaq.com) if I knew when CTP3 was due out.

SqlCredit - Part 4: Schema and Procedure Security
Rob Garrison
This is part 4 of a series. If you have not read part 3, you can find it here. In this month's installment, we will add security to the existing code and add new unit tests to prove that security.

CASE Function in SQL Server 2005 - part III
Muthusamy Anantha Kumar
In Part I and Part II of this series, we have illustrated how to use simple case expressions in queries. In this installment, I am going to illustrate how to use case functions in clauses such as group by.

How to Connect to a SQL Server Compact Edition Database with Management Studio
Julian Kuiters
Open a new connection by choosing File menu -> Connect Object Explorer in SQL Server Management Studio. Change the Server Type to SQL Server Compact Edition. For Database file: put the full path to an existing SQL Server Compact Edition .sdf file; click the drop-down button, and select New Database or Browse to locate a .sdf file. Enter a password if required.

Six Properties of Relational Tables
Pinal Dave
Relational tables have six properties:

SQL ERROR Messages - sysmessages error severity level
Pinal Dave
SQL ERROR Messages: Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error. The error severity levels provide a quick reference for you about the nature of the error. The error state number is an integer value between 1 and 127; it represents information about the source that issued the error. The error message is a description of the error that occurred. The error messages are stored in the sysmessages system table. We can see all the system messages running following statement in query analyzer.

Difference between Unique Index vs Unique Constraint
Pinal Dave
Unique Index and Unique Constraint are the same. They achieve same goal. SQL Performance is same for both.

SELECT vs. SET Performance Comparison
Pinal Dave
Usage: SELECT : Designed to return data. SET : Designed to assign values to local variables. While testing the performance of the following two scripts in query analyzer, interesting results are discovered.

Choosing between CLR and T-SQL stored procedures: a simple benchmark
Greg Larsen
In my previous article (Building my First SQL Server 2005 CLR) I wrote about my experiences coding and implementing my first SQL Server 2005 CLR procedure. The idea was to:

Troubleshooting with Dynamic Management Views
Eric Brown
If you work with SQL Server 2000, then you know how painful it is to triage a server that has "gone astray". In many cases, the error codes are undecipherable and the root cause of the problem is not easily found, let alone remedied. In fact, more times than not, a SQL 2000 DBA will simply do the unthinkable, and reboot the server.

SQL 2000 to SQL 2005: Where have all the old features gone?
Boris Baliner
SQL Server 2005 is out and everyone is moving to test and deploy it. However many people are still managing SQL Server 2000 instances. New author Boris Baliner brings us a few tricks to find that information you are used to from Enterprise Manager.

Joining on same table multiple times on different values
Joe Celko
SQL Apprentice Question: Having perfomance problems with this query (returning to many rows and takening to long), related I think to joining on the DomainDetail table 3 times. Is there a better way to do this?

Microsoft's Data Access Strategy
Mike Pizzo
Over the last 12 months, Microsoft has been talking a lot about two major innovations related to representing and querying data. The first is the new Entity Data Model exposed as part of the ADO.NET Entity Framework, and the second is a set of extensions to the .NET Framework for integrating queries into the programming language known as LINQ. What are these technologies, how do they relate to one another, and what role do they play in Microsoft's Data Access Strategy? Let's start with Microsoft's Data Access Strategy.

OLAP or Relational?
Chris Webb
If there's one big religious divide within the BI world, greater than the differences between vendors, then the question of whether to use an OLAP database or to query the relational database directly is it. Standing in the pro-OLAP camp as I do I probably have more in common with an Essbase guy than someone who wants to do BI with the SQL Server 2005 relational database exclusively. Anyway, here's an article by Ralph Kimball in Intelligent Enterprise that sums up the arguments on both sides pretty well:

Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios
Carl Rabeler
This article identifies the top 10 connectivity scenarios in which users may experience connectivity problems and explores error messages generated by common client applications. It shows you how to quickly isolate each error, and then provides the information you need to resolve the underlying issue that caused the particular error message.

SSAS: ProcessUpdate - Too much of a good thing
Darren Gosbell
I came across something interesting on the Analysis Services MSDN Forum a little while ago - this post has been on my todo list for a while :). When processing a large dimension (10 million+ members) the following error was encountered.

Generating lift reports using Reporting Services - Part 1
DMTeam
This tip explores a DMX extension introduced in SQL Server 2005 SP2 that can be used to render lift reports directly in Reporting Services.

How to find that a query could benefit from an index?
sqlmaster
Performance is most required achievement in a multi-user environment, using SQL Server 2005 this can be achieved with a few statments execution. Firstly you need determine the issues by finding which query or queries are causing issues in achieving performance.

Set up OLE DB source to read from View efficiently
Runying Mao
OLE DB source adapter is one of the most commonly used components in SSIS data flow task. In this article, we will discuss a very important performance observation about this adapter.

Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives
Runying Mao
BLOBTempStoragePath and BufferTempStoragePath are two properties on Data Flow Task. They define where on the hard disk(s) to page buffer data to. BLOBs (text, next, image) are written to BLOBTempStoragePath. All other buffer data will be swapped to BufferTempStoragePath. See the figure below for where these two properties are exposed in designer.

Something about SSIS Performance Counters
Runying Mao
SQL Server Integration Services provide a set of performance counters. Among them the following few are helpful when you tune or debug your package

Case Sensitive Searches in SQL Server
Decipherinfosys
Some time ago, we had blogged about doing case INsensitive searches in Oracle - you can read more on that post here. Oracle, by default is case sensitive whereas SQL Server installations by default are case-insensitive i.e. if you have a column called say FIRST_NAME and you want it to be unique, you can have SAM and sam as two values in it in Oracle where as in SQL Server, that will error out with a unique key violation.

Tracking SQL Server perfmon counters through T-SQL
Decipherinfosys
In SQL Server 2005, one of the new dynamic management views is : sys.dm_os_performance_counters. This DMV can be used to query the view directly and monitor the SQL Server related performance counters. Depending upon the services and applications installed, the number of counters will vary - these counters will range from memory counters to application specific counters…here are the ones that are on my local instance:

Presentation Skills for Business Intelligence - Nine Points of Roguery
Donald Farmer
A long post today, I hope it is interesting. It's funny how an idea can be dormant for ages, then suddenly crops up everywhere again. I used to have a simple method for structuring presentations - specifically, where I had to present results of an analysis, and often a related proposal. Most of us in the BI world do this regularly. I had not shared the technique much, but in recent weeks I have found myself describing it in detail several times, sitting down with hassled analysts helping them pull together summary presentations.

Using SQL Assertions for Database Unit Testing
James.Rowland-Jones
Recently I had a need to create some unit tests for a change to the database that was under development. I needed to change the database so that the NOT FOR REPLICATION option was set on all keys, constraints, indexes and triggers. Following an agile engineering best practice I created a schema unit test for each of these object types (more on this later) and ran the test. Naturally the test, as I expected, failed. I was now ready to make my change.

SSIS: Would you like to be able to execute a task from the command-line?
Jamie Thomson
When building packages in the SSIS Designer within BIDS you are able to execute a task or a container in isolation. This is really useful for development purposes.

Accessing and Updating Data in ASP.NET 2.0: Inserting Data
Scott Mitchell
ASP.NET 2.0's data source and data Web controls make working with data much easier than in previous versions. These controls allow for a declarative approach to working with data, without needing to write any cumbersome data access code. The articles we have explored in the series thus far have focused on understanding the core concepts of the data source controls and how to retrieve data from the database. In addition to retrieving data, the data source controls can also insert, update, and delete data.

Regular Expression Replace in SQL 2005 (via the CLR)
Jeff Smith
I had to do some data clean up the other day, and really needed some regular expression replacements to do the job. Since .NET has a great RegularExpressions namespace, and since SQL 2005 allows you to integrate .NET CLR functions in your T-SQL code, I thought I'd go ahead and experiment with creating a RegExReplace() function.

Thinking Set-Based .... or not?
Jeff Smith
So, I hear you're a "set-based SQL master"! As Yoda once said, you've "unlearned what you have learned". You've trained yourself to attack your database code not from a procedural, step-by-step angle, but rather from the set-based "do it all at once" approach. It may have taken weeks, months or even years to finally obtain this enlightened state of "database zen", but it was worth it. Your SQL code is short, fast, and efficient. There is not a cursor in sight. You have reached the point where you can write a single SELECT that replaces hundreds of lines cursors, temp tables and client-side processing. Life is good.

Will 64-bit increase the performance of my SQL Server application?
mssqlisv
With 64-bit servers becoming more mainstream and SQL Server customers running more deployments on native 64-bit Windows there is a common misperception that running on 64-bit will always provide an increase in performance over 32-bit. Many customers are surprised to find that some workloads actually run faster on 32-bit SQL Server than on the native 64-bit version. The assumption that 64-bit will always outperform 32-bit is not true and is determined largely by characteristics of the particular workload and hardware. Workloads that have a large volume of small uniform transactions using relatively simple query plans (OLTP-like in nature) which are not experiencing memory pressure may not benefit from running on native 64-bit SQL Server.

SaaS = Web Component + Desktop Component + Sync
Rafik Robeal
I tuned to Mix07 yesterday to listen to the keynote from Ray Ozzie. Ray draws the picture for the software and service model. One important statement in the keynote made feel really good, Ray said "Even software as a service vendors have found a need to expand their offering to include offline edition"

SQL 2000 to 2005 migration: execution plan change issue
Igor Kovalenko
Last week I was engaged on SQL 2000 to SQL 2005 migration project, and we had identified some issues related to significant changes in execution plans. Before migration we tested the application with using Upgrade Advisor (test passed without any caution), but hopefully we also had made an additional "trace replace" test. During this stage it was found that some stored procedures works fine in SQL 2000 but always failed in SQL 2005. Here is a script on "how to reproduce" :

SQL 2000 to SQL 2005 migration: temporary tables reuse issue
Igor Kovalenko
During the same project the next issue was found: temporary table reuse. Here is an example on "how to reproduce":

Retrieving Peristed Data Sent to Workflows
Brian Knight & Bayer White
There are two ways to communicate information with a workflow through its hosted runtime. First, data can be sent to the workflow via parameters. When a workflow starts up and runs it can process that data sent to it, however during the workflow's lifecycle, the initial data sent to the workflow can change. With short-running workflows where the workflow host continues to run while the workflow finishes, the workflow throws a "workflow completed" event and passes the processed data to the host.

Explanation SQL SERVER Merge Join
Pinal Dave
The Merge Join transformation provides an output that is generated by joining two sorted data sets using a FULL, LEFT, or INNER join. The Merge Join transformation requires that both inputs be sorted and that the joined columns have matching meta-data. User cannot join a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.

Random Number Generator Script - SQL Query
Pinal Dave
Random Number Generator: There are many methods to generate random number in SQL Server.

Connect to a Remote SSAS 2005 Database
Jacob Sebastian
Connecting to a SQL Server 2005 database is a simple process, but connecting to an Analysis Services 2005 database takes a bit more effort. Longtime data warehousing author Jacob Sebastian brings us a quick tutorial on how you can connect from Management Studio.

Synchronising Databases in SSAS 2005
Yaniv Mor
Planning for disaster recovery in SQL Server Analysis Services can be tricky. Fortunately SQL Server 2005 introduces the concept of a database synchronization, allowing you to transfer information and meta data from one server to another. Yaniv Mor brings us a look at this feature.

Quick list of VLDB maintenance best practices
Paul Randal
One evening last week I sat down with Kimberly for 5 minutes to come up with a top-of-our heads list of VLDB maintenance concerns for a company migrating a multi-TB database to SQL Server 2005. This isn't in any way based on the VLDB survey I've been doing (see previous posts) but is a common-sense list of things that everyone should do. People really liked the list so I'm posting it here. Maybe we should turn this into a book???

How to avoid "Orphaned Users" with SQL Server 2005 Database Mirroring
Glenn Berry
If you are thinking about using SQL Server 2005 Database Mirroring, (which works quite well), you will want to take steps to avoid the "orphaned" database user problem when you fail-over from Principal to Mirror or vice-versa.

Put SQL Server's EXISTS construct to good use
Tim Chapman
One thing I have noticed over the years is how seldom developers use SQL Server's very useful EXISTS construct. You can use EXISTS in a variety of situations -- some of which I will explore in this article.

Get to know new and enhanced business intelligence features in SQL Server 2005
sqlmaster
Microsoft is investing a lot to help the Business Intelligence (BI) platform users with the content and get acquaintance to the platform. You are aware BI includes SQL Server Analysis Services, Integration Services and Reporting Services, you can get to know by referring to the media presentation and learn about the enhanced features since the last release of these technologies in SQL Server arena.

RedPrairie Testing Proves Microsoft SQL Server 2005 Ready for Mission-Critical E2e Supply Chain Solutions
RedPrairie
MILWAUKEE & ORLANDO, Fla.-(Business Wire)-May 1, 2007 - RedPrairie Corporation, a world leading consumer driven optimization company, announced today at RedShift 2007, its Tenth Annual User Conference, the successful completion of performance and scalability testing of RedPrairie(R) E2e(TM) Supply Chain Execution solutions running on the Microsoft technology platform, including Microsoft SQL Server 2005 Enterprise Edition. The tests, conducted over a two-week period at Microsoft's Las Colinas, Texas, Testing Center, achieved over 500,000 order lines per hour processing speeds against a target of 300,000 order lines per hour. This is especially significant considering most order lines require execution of multiple SQL statements. These results complement previous performance and scalability testing at the same facility of RedPrairie Retail Productivity Solutions in which SQL Server 2005 FrontRunner status was achieved.

Dealing with case and accents in SQL Server
Arthur Fuller
Collation describes the code page, case sensitivity, accent sensitivity, and language or alphabet in use. I often see collation handled on a per-database scale. For instance, in my experience, most DBAs in North America go with the default collation, which is case insensitive. The collation has no effect on the data itself, but rather how it is compared and indexed.

Parallel Statistics Update
Merrill Aldrich
Recently in my own practice, I was abruptly reminded of the value of SQL Server's Update Statistics with Full Scan: to make a long story short, we had a system that was getting a bad query plan that would consistently peg one of the CPUs for hours at a time. If multiple requests for the same data came in, more CPUs would be taken up, eventually using 100% of all processors. The query was a simple one, and we were stumped by this issue for a time. It turned out that the maintenance on the database in question just had too low a sampling rate for Update Statistics, which had the effect of causing the optimizer to choose a bad plan. Changing to Update Statistics with Full Scan solved the issue.

[В начало]

ФОРУМ SQL.RU

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

Вышел русский SQL Server 2005!!!
Microsoft SQL Server. Полезные алгоритмы от SQL.RU (+CD)
Помогите оптимизировать запрос.
и снова deadlock :-(
как рассчитать визиты
создание архива с помощью WinRAR.exe
MSSQL XML Internet
SQL Server Agent пытается подключиться к непонятному серверу.
Не удаеться подключиться к SQL Server 2000
Как подавить SELECT который делает хранимая процедура?
Сервер под XP
MS SQL 2005 не жрёт больше 1066Мб
Как генерировать записи?
Как сделать уникальные значения в поле среди которых могло бы быть повторяющееся NULL
Transact-SQL
Выборка наибольшего значения из заданного периода
Триггер помогите исправить
Вывод записей постранично?
Кодировка
1С+SQL 2000

[В начало]

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

Realationship
Scetciki optimizacii
Выбор сервера для SQL2005
Full MaintenancePlan
Проблема с включением полнотекстового поиска в Sql 2000
Mirroring: выход из состояния DISCONNECTED
2005 Databases Diagrams
Стала доступна для загрузки Windows Server Code Name "Longhorn" Beta 3
Report Services: RunningValue in Subtotals(matrix)

[В начало]

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