Отправляет email-рассылки с помощью сервиса Sendsay

MS SQL Server

  Все выпуски  

MS SQL Server - дело тонкое...


Информационный Канал Subscribe.Ru


#164<<  #165

СОДЕРЖАНИЕ

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

СОВЕТЫ

Использование метаданных SQL Server

По материалам статьи Bill Graziano: Using SQL Server's Metadata

Представления информационных схем являются частью стандарта SQL-92. Этот стандарт определяет ряд представлений, которые призваны предоставлять информацию о базе данных. Например, есть представление по имени TABLES, которое предоставляет информацию о таблицах базы данных. Вы можете сделать запрос к этому представлению, точно так же как и к любому другому представлению. Например:

SELECT * FROM pubs.INFORMATION_SCHEMA.TABLES

Этот запрос возвратит информацию обо всех таблицах и представлениях в базе данных pubs:


TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE 
------------- ------------ ---------- ---------- 
pubs          dbo          authors    BASE TABLE
pubs          dbo          discounts  BASE TABLE
 ...
pubs          dbo          titleview  VIEW

Кроме обычного набора таблиц могут быть отображены несколько системных, которые используются для репликации. Это такие таблицы, как sysarticles и syspublications. TABLE_CATALOG - это имя базы данных, а TABLE_SCHEMA - владелец объекта. Обратите внимание, что владельцем представления INFORMATION_SCHEMA является dbo.
Другим интересным представлением является COLUMNS. Например:


SELECT TABLE_CATALOG,
       TABLE_SCHEMA, 
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH
FROM pubs.INFORMATION_SCHEMA.columns
WHERE table_name = 'authors'

Возвратит информацию обо всех столбцах таблицы authors:


TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE  CHARACTER_MAXIMUM_LENGTH 
------------- ------------ ---------- ----------- ---------- ------------------------ 
pubs          dbo          authors    au_id       varchar    11
pubs          dbo          authors    au_lname    varchar    40
pubs          dbo          authors    au_fname    varchar    20
pubs          dbo          authors    phone       char       12
pubs          dbo          authors    address     varchar    40
pubs          dbo          authors    city        varchar    20
pubs          dbo          authors    state       char       2
pubs          dbo          authors    zip         char       5
pubs          dbo          authors    contract    bit        NULL

На практике, можно получить немногим больше информации, чем из этого представления, результаты работы которого представлены выше. Представление выводит по одной строке для каждого столбца таблицы в порядке его позиции; показывает допустимость null-значений; видна точность числовых значений; можно увидеть значения по умолчанию; какой используется набор символов; тип сортировки, его порядок и любую другую информацию относительно определяемого пользователем типа данных. Ниже представлен список всех представлений INFORMATION_SCHEMA:

CHECK_CONSTRAINTS - Предоставляет информацию обо всех ограничениях в базе данных. COLUMN_DOMAIN_USAGE - Показывает, какая колонка и в какой таблице является определяемым пользователем типом данных. COLUMN_PRIVILEGES - Выводит по одной строке для каждой привилегии уровня столбца, предоставленной текущему пользователю. COLUMNS - Выводит по одной строке для каждого столбца в каждой таблице или представлении базы данных. CONSTRAINT_COLUMN_USAGE - Выводит по одной строке для каждого столбца, которому сопоставлено ограничение. CONSTRAINT_TABLE_USAGE - Выводит по одной строке для каждой таблицы, которая имеет ограничения. DOMAIN_CONSTRAINTS - Выводит определяемые пользователем типы данных, которым сопоставлены правила. DOMAINS - Выводит определяемый пользователем тип данных. KEY_COLUMN_USAGE - Выводит по одной строке для каждого столбца, который определен как ключ. PARAMETERS - Выводит по одной строке для каждого параметра в хранимой процедуре или определяемой пользователем функции. REFERENTIAL_CONSTRAINTS - Выводит по одной строке для каждого ограничения внешнего ключа. ROUTINES - Выводит по одной строке для каждой хранимой процедуры или определяемой пользователем функции. ROUTINE_COLUMNS - Выводит по одной строке для каждого столбца, возвращаемого любыми таблице-возвращающими функциями (table-valued functions). SCHEMATA - Выводит по одной строке для каждой базы данных. TABLE_CONSTRAINTS - Выводит по одной строке для каждого ограничения, определенного в текущей базе данных. TABLE_PRIVILEGES - Выводит по одной строке для каждой привилегии уровня таблицы, предоставленной текущему пользователю. TABLES - Выводит по одной строке для каждой таблицы или представления в текущей базе данных. VIEW_COLUMN_USAGE - Выводит по одной строке для каждого столбца в представлении, включая базовые таблицы этого столбца, когда это возможно. VIEW_TABLE_USAGE - Выводит по одной строке для каждой таблицы, используемой в представлении. VIEWS - Выводит по одной строке для каждого представления. В Books Online каждое из этих представлений описано более детально, включая полное описание возвращаемых ими результатов.

[В начало]

Функции метаданных

SQL Server также имеет ряд функций, которые возвращают информацию об объектах в базе данных. В качестве наглядного примера, рассмотрим использование функции ColumnProperty. Выполним следующий запрос в базу данных Northwind:

SELECT COLUMNPROPERTY( OBJECT_ID('Categories'),'CategoryID','IsIdentity')

В результате исполнения этого запроса возвращается 1, которая указывает, что столбец CategoryID является Identity. Существуют также другие, дополнительные функции, которые призваны выдавать информацию об Identity столбцах. С помощью функции ColumnProperty можно проверять довольно большой набор свойств, включая: допустимость null-значений, точность, масштаб и т.д. Многие из этих свойств также доступны и представлениях информационной схемы, но некоторых там нет. В Books Online имеется полный список функций метаданных.
В представленном выше запросе, Мы использовали ещё одну функцию метаданных - OBJECT_ID. Многие из функций метаданных принимают в качестве указателя на объект только его идентификатор (ID). В таких случаях, удобно использование этой функции, которая как раз и возвращает необходимый ID объекта, указанного в её параметре по имени. Обратной для этой функции является функция OBJECT_NAME, которая возвращает по ID объекта его имя.
Другая удобная функция - ObjectProperty, которая работает аналогично ColumnProperty, но имеет намного больше свойств, которые можно проверить. Например, следующий запрос покажет таблицы, которые имеют Identity, кластерные индексы и первичные ключи.


SELECT  TABLE_NAME, 
  IDNTY = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasIdentity'),
  CLSTRD = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasClustIndex'),
  PK = ObjectProperty(OBJECT_ID(TABLE_NAME), 'TableHasPrimaryKey')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'

Так Вы можете делать проверку того, действительно ли первичный ключ является кластерным индексом. Этот способ удобен для проектов, в которых разработчики могут создавать собственные таблицы. Дополнительные функции включают: IndexProperty, DatabaseProperty, FileGroupProperty, FullTextProperty и некоторые другие, хорошо описанные в Books Online.

[В начало]

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

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

Десятичный разделитель в DBGrid
Grig
Национальные стандарты - хорошо для юзеров, а для программистов - головная боль. Нужно следить, чтобы все компьютеры, работающие с нашими программами, были одинаково настроенными. Чаще всего проблемы возникают с десятичным разделителем. Точка или запятая - неприятная проблемка!...
Очистка метаданных в репликации слиянием
Michael R. Hotek
Таблица MSmerge_contents может стать проблемой для любого администратора баз данных, который обслуживает репликацию слиянием (Merge) достаточно много времени. Проблема состоит в том, что MSmerge_contents будет постоянно расти, если этим не управлять. Merge репликация очень интенсивно использует MSmerge_contents, что и вызывает проблемы, поскольку её размер неуклонно растёт. Это обычно проявляется в виде взаимоблокировок и существенного замедления обмена изменений в репликации. Происходит это потому, что время поиска искомых строк в таблице метаданных увеличивается вместе с увеличением её размера...
Планирование репликации с использованием identity
Michael R. Hotek
Автор статьи очень часто встречает вопросы о том, что использование поля identity порождает проблемы в репликации. Но он убеждён, что колонка Identity вполне может быть использована в репликации, просто необходимо выполнить некоторое предварительное планирование, наряду с установкой редко используемой опции. Эта статья описывает хороший метод, который автор использовал несколько лет, и который позволил ему успешно использовать колонки identity...
Автоматическое управление диапазонами Identity в репликации слиянием
Александр Гладченко
Эта статья посвящена анализу работы механизма MS SQL Server 2000, обеспечивающего автоматическое разбиение диапазонов полей identity для участвующих в репликации слиянием таблиц на издателе и подписчиках. Суть решаемой этим механизмом проблемы состоит в том, что значения identity могут устанавливаться и издателем и подписчиком самостоятельно. Часто, такие поля, как identity используются в качестве внешнего ключа. Если тиражировать эти значения средствами репликации слиянием, может произойти нарушение ограничения внешнего ключа. Одинаковые значения, полученные на разных серверах, после слияния вызовут возникновение конфликта. Традиционно, проблема identity в репликации решается путём использования для этого опции NOT FOR REPLICATION и разделения его значений на не пересе...
Понимание SOAP
Шатохина Надежда
Изначально SOAP расшифровывался как "Протокол доступа простых объектов" (Simple Object Access Protocol). Если бы вы спросили кого-нибудь о том, что значит SOAP, несколько лет назад, то получили бы примерно такой ответ:"это для работы DCOM и Corba (т.е. RPC вызовов) через Internet". Первые авторы признавали тогда, что они фокусировались на "доступе к объектам", но со временем захотелось, чтобы SOAP обслуживал более широкую аудиторию. Таким образом, фокус спецификации быстро сместился с объектов на обобщенную оболочку обмена XML сообщениями...

[В начало]

Новые и обновлённые технические статьи Microsoft

"HTTP Error 401.3 Access Denied" Error Message When You View a SQL Server 2000 Service Pack 2 Database on Windows Server 2003
BUG: Query Analyzer May Not Display All Results with Some Error Sequences
BUG: SQL-DMO: ExportData Method of BulkCopy Object Causes VB Run-Time Error
DOC: Loopback_detection Option is Not Clearly Explained in SQL Server Books Online
FIX: 3600 Errors Written to ERRORLOG With INSERT..EXEC of xproc
FIX: A Memory Leak Occurs When Cursors Are Opened During a Connection
FIX: Abort of Bulk Insert May Result in Error Message 8979 from CHECKDB
FIX: Abort of DTC Transaction in tempdb May Cause 7105 Errors, Assertions, and Possible Shutdown of SQL Server
FIX: Access Violation May Occur If You Log Off After You Cancel A Linked Server Query
FIX: Access Violation with Queries that Involve Hash Join on Large Data Sets
FIX: Access Violations May Occur with SQL Mail When You Use XP_SENDMAIL
FIX: ActiveX Controls for Replication May Cause Access Violation with More Than 256 Articles in a Publication
FIX: ADO Applications Report a Syntax Error When Column Names Contain Spaces
FIX: An Access Violation Occurs When a Cursor is Defined on a Query That has Nested Subqueries
FIX: Attention Events that Occur During Blocking May Cause SQL Server to Stop Responding, Cause AVs or Other Errors
FIX: AV on DECLARE CURSOR with Correlated Subquery and KEYSET or DYNAMIC Cursor Type
FIX: BULK INSERT Replaces Current Datetime Column Value with Previous Column Value if Data File Contains All Spaces
FIX: Cardinality Estimation of ISNULL Function Does Not Account for Null Values
FIX: CASE Using LIKE with Empty String Can Result in Access Violation or Abnormal Server Shutdown
FIX: Client Disconnects Can Cause SQL Server Instability When Using More Than Two Gigabytes of Memory
FIX: Code Point Comparison Semantics for SQL_Latin1_General_Cp850_BIN Collation
FIX: COM May Not Be Uninitialized for Worker Thread When You Use sp_OA
FIX: Complex ANSI Join Query with Distributed Queries May Cause Handled Access Violation
FIX: Complex CASE Clause on Query May Cause Error 8621 and Terminate Batch
FIX: Complex Join Query with Views and Redundant Tables May Cause Error 8623
FIX: Cursor Created on Linked Server That Requests Meta-Data May Cause Handled Access Violation
FIX: Cursor Fetches Duplicate Row After Positioned Update
FIX: Cursors That Have a Long Lifetime May Cause Memory Fragmentation
FIX: Deferred Updates, Triggers and READ UNCOMMITTED May Leak Memory
FIX: Delayed Domain Authentication May Cause SQL Server to Stop Responding
FIX: Dimension Security on a Cube with Distinct Count May Return #ERR
FIX: Distributed Query with NO_BROWSETABLE Against Timestamp Column Can Cause Access Violation
FIX: Distribution Agent May Experience an Access Violation in a Republisher Topology
FIX: Distribution Task Encounters Syntax Error During Application of Initial Snapshot
FIX: Dump Database Logs flush_cache Errors and Does Not Complete
FIX: Error 1203 and Failed Assertion Occurs When You Run a Query with a Parallel Plan
FIX: Error 1203 Occurs When Trigger Fires on Insert to Table with Foreign Key Constraint
FIX: Error 1203 Occurs with Trace Flag 5302
FIX: Error Message 1203 Followed by Stack Overflow With Keyset Cursors
FIX: Error Message 7355 "..Inconsistent metadata for a column" Occurs When You Use a Linked Server
FIX: Error Message 8624 "Internal SQL Server" Occurs with Cold Index Retrieval OLE DB Provider
FIX: Error Message: "The specified index already exists" May Occur When Re-initializing Subscription with SQL Server CE Edition
FIX: Exception Access Violation Encountered During Compile of Hash Match Team Plan
FIX: Exception Access Violation Encountered in PrintWarnings Function
FIX: Execution of sp_OACreate on COM Object Without Type Information Causes Server Shut Down
FIX: Filter Not Pushed Below Aggregate If Subquery Uses the DISTINCT Keyword
FIX: General Network Error When You Try to Back up or Restore a SQL Server Database on a Computer That Is Running Windows Server 2003
FIX: Infinite Loop During Full-Text Catalog Population
FIX: Input Parameters to the OLE Automation (sp_OA) Stored Procedures Truncate at 4000 Characters
FIX: ISNULL with ANSI_WARNINGS OFF Drops Connection On Divide By Zero
FIX: Joins with a UNION ALL View May Take a Long Time to Compile and May Use Large Amounts of Memory
FIX: Latch Timeout Warnings Appear in Error Log and Slow SQL Server Response Occurs with AWE Enabled
FIX: Linked Index Server Query Through OLE DB Provider with OR Clause Reports Error 7349
FIX: Linked Server Query with Hyphen in LIKE Clause May Run Slowly
FIX: Lock Timeout on Scrollable Cursor May Occur if Page Locking is Disabled
FIX: Merge Join with Rank Operator May Produce Incorrect Results
FIX: Merge Replication Non-Convergence Occurs When You Use the "Subscriber Always Wins Conflict Resolver" Option
FIX: Merge Replication Non-Convergence Occurs with Local Subscribers when Published Table has 32 or More Columns
FIX: Miscalculation of CPU Time Occurs If SQL Server Uses a New Thread to Execute a Batch on a Connection That Already Exists
FIX: Mssearch.exe Memory Leak Occurs When Results Are Not Completely Processed
FIX: Multiple SQL Agent Jobs Launched At The Same Time May Fail With Primary Key Error Message
FIX: New Trace Flag to Enhance Stored Procedure Replication
FIX: Nonclustered Indexes Rebuilt For CREATE UNIQUE CLUSTERED INDEX ... WITH DROP_EXISTING Query
FIX: NT User and Domain Name May Not Be Recorded by SQL Profiler for a Standard Security Connection
FIX: Opening the Database Folder in SQL Server Enterprise Manager 2000 Takes a Long Time
FIX: Optimizer Slow to Generate Query Plan for Complex Queries that have Many Joins and Semi-Joins
FIX: OR of Multiple Expressions with Common Subexpressions May Choose Poor Plan
FIX: Parallel Query May Cause an Access Violation in CBitSet::Incl
FIX: Parallel Query May Fail with Error Message 1203 and an Assertion
FIX: Parameterized Query with Several IN Clauses Fails with Error: 8623
FIX: Properties Dialog Box of a Transform Data Task Takes a Long Time to Open
FIX: Pull Subscribers Experience Non-Convergence After Running sp_mergecleanupmetadata Against a Published Database
FIX: Query Compilation Fails with Error 701 After It Uses Up Buffer Pool Memory
FIX: Query with Complex Correlated Subquery May Cause Unexpected Results
FIX: Query with Multiple Aggregate Functions and Subqueries May Encounter AV
FIX: Query with OUTER JOINs May Neglect Built-in Function in WHERE Clause
FIX: Querying Syslockinfo with Large Numbers of Locks May Cause Server to Stop Responding
FIX: Querying Sysmembers Might Result in a Leak of Buffer Pool Memory
FIX: Resource Contention Occurs When You Update Multiple Tables That Have a Timestamp Column
FIX: Retrieves Inconsistent Results Returned by Indexed Access OLEDB Provider
FIX: Return Code of xp_findnextmsg Changed to 1 Against an Empty Inbox
FIX: ROLLBACK of Transaction May Abort Concurrently Active Transactions
FIX: Scripting Object with Several Extended Properties May Cause Exception
FIX: Second SQLExecute on Prepared Statement with SQL_NOSCAN_ON Causes Client-Side AV
FIX: SELECT INTO with TEXT/IMAGE Data Is Not a Non-Logged Operation
FIX: Selection of a Text Column From Keyset Cursor Defined as Join Might Cause Access Violation
FIX: Sending Open Files as Attachment in SQL Mail Fails with Error 18025
FIX: Shared Table Lock Incorrectly Held for Lifetime of Transaction Instead of Statement
FIX: Shared Table Lock Is Not Released After Lock Escalation
FIX: Slow Compile Time and Execution Time with Query That Contains Aggregates and Subqueries
FIX: Snapshot Delivery Failure During BCP IN Not Recognized by Merge Agent
FIX: SP_OA Procedures Leak Memory and Cause Various Errors
FIX: Spanish VSwitch Causes Setup to Fail
FIX: SPID May Stop Responding with CPU Spin During Log Out When it Drops Temp Procedure
FIX: SPID May Stop Responding with Status of SPINLOOP During a Rollback Operation
FIX: SQL Mail Operations May Lead to Failures and Memory Leaks
FIX: SQL Profiler Causes Handled Access Violation in SQL Server While Tracing Large Data
FIX: SQL Server 2000 May Be More Aggressive with Read-Ahead Than SQL Server 7.0
FIX: SQL Server CE Subscribers May Encounter Unnecessary Network Traffic on Synchronization
FIX: SQL Server CE Subscribers May Receive Duplicate Changes on Synchronization
FIX: SQL Server Fails to Start When Computer Binds 48 or More Socket Addresses
FIX: SQL Server May an Encounter Access Violation During a BULK IMPORT
FIX: SQL Server Process Terminates Attempting to Reset from an sp_OA Based Batch
FIX: SQL Server Service Manager Fails with "The system cannot find the file specified" Error Message
FIX: SQL Trace May Cause Heap Corruption After Handled Exception
FIX: SQLDMO Transfer Operation Truncates Transaction Log of Destination Database
FIX: SQLTrace Start and Stop is Now Reported in Windows NT Event Log for SQL Server 2000
FIX: Subquery That Returns One Row May Cause CPU Spin and Excessive Optimizer Memory Usage
FIX: The Distribution Agent Might Shut Down with a Syntax Error or Queued Updating Subscribers Might Become Unsynchronized
FIX: The Distribution Agent Shuts Down and Error 2627 or Error 2601 Are Generated When You Apply a Command to a Queued Updating Subscriber
FIX: The Replication Queue Reader Agent Utility Shuts Down After Error 2601 Occurs
FIX: The SQL-DMO Transfer Method Silently Fails to Copy All Data
FIX: Transaction Log of Snapshot Published Database Cannot Be Truncated
FIX: Tuning Wizard May Fail When You Specify a Workload File for Non-dbo Owned Objects
FIX: Undetected Deadlock Occurs When Lock Owner Is Waiting on CXPACKET
FIX: Unexpected Results Occur When You Use a Sort and a Stream Aggregate
FIX: UNION Query Returns Internal Query Processor Error Message 8623
FIX: UPDATE After ALTER TABLE Can Result in Unexpected Data to Added Columns
FIX: Update Conflict for Unique Key Columns for Queued Updating Subscriber Results in Non-Convergence
FIX: Updates That Run at a Read Uncommitted Isolation Level Fail with a 644 Error Message
FIX: Use of a Dynamic API Server Cursor with a NOLOCK Hint Causes Error 1047
INF: Behavior of SQL Server 2000 Network Library During Dynamic Port Detection
INF: Comparing SQL Collations to Windows Collations
INF: SQL Server 7.0 Service Pack 4 Readme.htm Additions
Overview of the SQL 2000 Critical Update Wizard
PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems
PRB: Cost of Using Non-Clustered Indexes May Be Incorrect if Data Correlation Exists
PRB: DBCC SHRINKFILE and SHRINKDATABASE Commands May Not Work Because of Sparsely Populated Text, Ntext, or Image Columns
PRB: Maintenance Job Fails on Named Instances of SQL Server 2000 with Error 22029
PRB: Population or Query of a Full Text Catalog May Stop Responding
PRB: Slow DELETE or UPDATE Against Non-SQL Linked Server
PRB: sp_grantlogin "NT AUTHORITY\SYSTEM" Fails on Microsoft Windows NT 4.0
PRB: SQL Server 1355 Warning Errors are Informational
PRB: SQL Server CE Web Service Extensions Are Removed
PRB: SQLMail Is Not Supported When You Run the Server in Fiber Mode
PRB: There May Not Be Enough Virtual Memory with Large Number of Databases

[В начало]

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

Auditing DTS Packages
Haidong Ji
I received quite a few emails from readers who read my article on managing and scheduling DTS packages. Many readers like the idea of using a SQL Server login so that all packages are owned by the same account. Therefore a group working on the same project can edit each other's packages. However, many also asked the question of auditing. Sometime we want to know which person in a group that share the same SQL login edited the package. I think this can be useful in some situations
Managing Your Jobs
Randy Dyess
Having he honor of working for quite a few companies that did not have the resources to buy any of the nice SQL Server toys that exist out there or were willing to put an email client on the servers, I have found myself spending a good deal of time each morning checking the status of the numerous jobs running on my servers. Not a hard thing to accomplish, but very time consuming when you are talking about dozens of servers with hundreds of jobs. Maybe it was just me, but no matter how much I pleaded at some of these companies, they would go through the red-tape to get an email client put on the SQL Servers so I could use the job notification ability to send me a nice email each morning if a particular job failed. Being the poor companies DBA, I had to come up with something else
Index Fragmentation and Its Resolution
Randy Dyess
While there is no doubt of the benefits of adding indexes to your tables and for the most part you have to do little work to keep the indexes maintained. Some maintenance is required as indexes can become fragmented during data modifications and this fragmentation may become the source of performance issues with your queries
Identify and delete duplicate data from a table
Randy Dyess
Recently I was asked to help someone clean up their database after they had double loaded an import file. The problem they were having in identifying and deleting the duplicate information was the fact that a timestamp is applied to each row of data as it is inserted into the table. While the rest of the row of data was duplicated, the timestamp made the row unique. It was this uniqueness that caused the simple methods of determining and deleting duplicate data to fail. They needed a way to delete data from a table in which they determine the criteria of what made the data duplicate
SQL Server Startup Parameters
Randy Dyess
Every now and then we all have to reread something we may already know because frankly if you are like me, you can't keep everything in your head. I tend to go back over tips and tricks and even database concepts when I have time so I will maybe remember enough about it so when a problem arises I at least know where to go look and find the answer. It is with, that I want to write about my latest re-education attempt
Basic Plan to Optimize and Tune Queries
Randy Dyess
During a recent interview to find a new job before my contract ran out, I was asked a question that took me by surprise. The interviewer simply asked me what steps I took to decide which stored procedures needed optimizing and what steps I used to optimize those queries. What took me by surprise was not the question; it was the realization that I did not have a "formal" plan of attack to determine stored procedures that needed to be optimized or a plan to actually optimize them. After the interview, I decided to formalize the basic steps I used to find stored procedures to optimize and the steps I took whenever I decided a query needed to be optimized. After jotting down some notes, I decided that I would turn this "plan" into an article so others would know what basic steps are needed to optimize a query
Save Those Procedure Texts
Randy Dyess
Have you ever made the mistake of recreating an existing stored procedure without saving the old version? I have, lucky for me I had a backup database that I could retrieve the old version when the new one failed. This mistake lead me to search for methods of keeping multiple versions of my stored procedure text around so I could be sure that I had the last few versions to track any bugs or logic problems that just suddenly showed up
Manage Multiple SQL Server Installations and Databases with OSQL
Randy Dyess
At one point in time I was tasked with running over 70 databases on more than a dozen servers without any of the costly third-party tools that exist to accomplish such tasks and I often get asked how can you handle such large numbers of databases. This is a small article that will show you how to use the handy OSQL command line utility provided with SQL Server to execute repetitive tasks against large numbers of databases or manage large numbers of servers. The OSQL command line utility allows you to execute Transact-SQL statements, system procedures, and script files against multiple servers and databases by using ODBC connections to those servers and databases. The syntax of the OSQL utility allows you to specify which server, database, login, password, input file, output file, as well as formatting functions you want to run with your SQL script. By creating a small bat file containing this information for the servers and database you manage and creating a SQL script file, you can easily perform repetitive SQL tasks with one or two mouse clicks
Interact with SQL Server's Data and Procedure Cache
Randy Dyess
As you work with SQL Server you have probably heard of the terms data cache and procedure cache and may have wondered what exactly a cache was. SQL Server is configure to use a pool of memory of the server and it will allocate the majority of this memory pool to hold both data pages that have been read and the compiled execution plans for all Transact-SQL statements. It is this dynamic pool of memory that is being referred to and the data cache and procedure cache, keep in mind that in versions of SQL Server before SQL Server 7.0 the data cache and procedure cache were two separate pools of memory and could be controlled separately, today one pool of memory is used both for data and execution plans
Create and Manage SQL Server Stored Procedures using Transact-SQL
Randy Dyess
As we learn more about SQL Server and Transact-SQL it is easy to become overwhelmed with the amount of information out there. I can recall hundreds of times when I have said, "I know there is a (stored proc, DBCC command, SQL statement, ect) out there that does that, I just can't remember its name." I thought I would deviate from my normal article style and see if writing an article that groups stored procs, DBCC commands, and SQL statements by their main or secondary usage would help others. My current job involves creating and maintaining hundreds of stored procedures so I thought I would put together objects that I knew about that are used in the creation and maintenance of SQL Server stored procedures and extended stored procedures in SQL Server 7.0 and 2000 (I'm not going to list or discuss objects used to obtain users permissions as I feel that those objects are best left for another article)
SQL Server Trace Flags
Randy Dyess
Trace flags are used to temporarily enable or disable specific database functions allowing you to change default database behavior or to observe hidden database behavior and will remain in effect until they are either manually turned off or SQL Server is restarted. Keep in mind that most trace flags are undocumented and Microsoft can and will change the behavior of the flag between SQL Server versions, service packs or hotfixes
Auditing Your SQL Server Environment Part II (Reviewing your Role Membership)
Randy Dyess
Over the last few years I have been a roving SQL Server DBA contractor and tended to work contacts in small and mid-size companies that involved organizing, documenting and then tuning/optimizing existing SQL Servers in those companies. I have noticed one very disturbing truth over the years; no one seems to document anything. In fact, I was usually thankful if I could find something or someone who knew the sa passwords on each SQL Server installation, let alone knew anything else about their setups. I have often been asked how I could go into a company that had dozens of servers, over 50 SQL Server databases, no existing documentation, no full-time DBA on staff, and no documentation and ramp up to a functioning level in a very short time frame. My answer was practice and my file of stored procedures w ritten over the years that I carried with me which allowed me to do quick audits of the SQL Server installations and databases so I could quickly produce documentation to work from. This article is the second article in a series that I intend to post on my website to share those stored procedures and methods I have learned with you to help you produce and learn a new environment if you move on or obtain another project at your existing company
SQL Server 2000 Table Hints
Randy Dyess
As you advance in your skills as a Transact-SQL developer or SQL Server database administrator there will come a time when you need to override SQL Server's locking scheme and force a particular range of locks on a table. Transact-SQL provides you with a set of table-level locking hints that you can use with SELECT, INSERT, UPDATE, and DELETE statements to tell SQL Server how you want it to lock the table by overriding any other system-wide or transactional isolation level
Is a Temporary Table Really Necessary?
Randy Dyess
You will find articles on this site as well as others advising you to avoid the use of temporary tables to maximize the performance of your queries. I agree with the articles, but would like to add that sometimes you cannot avoid the use of a temporary table. For those of you who have not read my Bio, I work with some very large SQL Server databases (the biggest being over 2.2 terabytes) and have found that I can avoid the use of temporary tables in most cases but sometimes they come in handy. This article will discuss the use and the alternatives to temporary tables from a query performance and maintenance standpoint
Auditing Your SQL Server Environment Part I
Randy Dyess
Over the last few years I have been a roving SQL Server DBA contractor and tended to work contacts in small and mid-size companies that involved organizing, documenting and then tuning/optimizing existing SQL Servers in those companies. I have noticed one very disturbing truth over the years; no one seems to document anything. In fact, I was usually thankful if I could find something or someone who knew the sa passwords on each SQL Server installation, let alone knew anything else about their setups. I have often been asked how I could go into a company that had over 50 SQL Server databases up and running on a dozen or more servers, that had no existing documentation, and that had no full-time DBA on staff or had lost their DBA several months ago and a developer or manager was filling in, and ramp up to a level that I could function in a very short time frame. My answer was practice and my file of stored procedures written over the years that I carried with me which allowed me to do quick audits of the SQL Server installations and databases so I could quickly produce documentation to work from. This article is the first in a series that I intend to post on my website to share those stored procedures and methods I have learned with you to help you produce and learn a new environment if you move on or obtain another project at your existing company
Optimizing Stored Procedures To Avoid Recompiles
Randy Dyess
One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored procedures. Recently started looking at this issue where I worked and stored procedures gathered from other clients and I noticed that there is an issue with SQL developers not taking recompiles into account when they create new code. When a stored procedure recompiles it will place a compile lock on the objects referenced by the stored procedure and if there are enough stored procedure recompiles the database may experience blocking. While all databases will experience stored procedure recompiles as a normal matter of database operations, it is when a stored procedure recompiles with every run that a database administrator or Transact-SQL developer needs to look out for and determine a remedy. A database getting thousands of recompiles an hour will suffer in performance and show short term blocking that will affect the database users. Will the query optimizer in SQL Server 2000 seems to have been greatly enhanced in its ability to reuse cached procedure plans, a few of the following problems will still show up in SQL Server 2000
Book Excerpt: Database Access with Visual Basic.NET, 3rd Edition
Jeffrey McManus, Jackie Goldstein
This book provides real-world solutions to the data-access issues VB.NET developers face every day and shares the authors' secrets for becoming a more effective database programmer using .NET technologies. Chapter 8 explains the use of Visual Studio Database Projects for managing SQL scripts
Linked Servers on MS SQL: Part 1
Don Schlichting
Think of a Linked Server as an alias on your local SQL server that points to an external data source. This external data source can be Access, Oracle, Excel or almost any other data system that can be accessed by OLE or ODBC--including other MS SQL servers. An MS SQL linked server is similar to the MS Access feature of creating a "Link Table."
Accessing Shared Resources Using ASP.NET
Anthony Arslan
Accessing shared resources is a challenge for many ASP.NET developers. This challenge is encountered when attempting to develop Web forms and Web services. Microsoft .NET has a new approach to user authentication and authorization, which is generally a subject covered under security in most programming books and magazines. Most security articles and books dedicate a large portion of their resources explaining forms authentication. However, understanding the ASP.NET security architecture in conjunction with IIS, Network, and Operating System security is the key to providing the best solution
A Look at Microsoft's "Web Data Administrator"
John Peterson
Do you spend part of your day administering a remote SQL Server? If so, has Microsoft's got the tool for you. Introducing Microsoft's Web Data Administrator
Microsoft enhances FrontPage and SQL Server
Paul Krill
Microsoft is to support development of XML-based websites in Office FrontPage 2003 and extend SQL Server database capabilities to more than 50 proprietary databases and files
Using SQL Server's Metadata
Bill Graziano
The Information Schema views are part of the SQL-92 standard. The SQL-92 standard defined a number of views that would provide information about the database. For example, there's a view called TABLES that provides information about the tables in a database. You can query it just like any other view. The query
Deleting Duplicate Records
Bill Graziano
First, I'll need some duplicates to work with. I use this script to create a table called dup_authors in the pubs database. It selects a subset of the columns and creates some duplicate records. At the end it runs a SELECT statement to identify the duplicate records
The SQL_VARIANT Datatype
Bill Graziano
Printer Friendly Version A sql_variant can hold any datatype except text, ntext, image, and timestamp without conversion. For example, the following code will run cleanly
Understanding Identity Columns
Bill Graziano
In it's simplest form an identity column creates a numeric sequence for you. You can specify a column as an identity in the CREATE TABLE statement
SQL Server Cursor Performance
Bill Graziano
A recent article on 4GuysFromRolla.com had an ASP script to generate the Transact-SQL code for a cursor. I've always thought that cursors where slower than set-based SQL but I never knew how much slower. Read on for the results and a couple of suprises thrown in. This article was originally published on SQLTeam.com
Returning Data from Stored Procedures
Bill Graziano
This article discusses three common ways to return data from stored procedures: OUTPUTing variables, temp tables and the RETURN statement. This article was originally published on SQLTeam.com. All the examples in this article use the pubs database and MS SQL Server 7. Everything in this article also works in SQL Server 2000. SQL Server does give you some additional options which I'll mention in the article
Performance Monitor
Windows NT and 2000 come with a handy tool to help you evaluate how well your computer is performing. This is the Performance Monitor. Actually, Performance Monitor is little more that a graph-drawing tool, the clever bit is in the applications you want to monitor, which expose counters to the performance tool that you can choose to look at. Because the architecture is constructed this way, with each application exposing it's own counters to performance monitor, then Performance Monitor is by definition extendable
Create a Web Portal Module
Stan Schultes
Extend the open source DotNetNuke ASP.NET portal with an add-in module for collecting user suggestions. ASP.NET's many advantages include a compiled code environment, excellent caching and security mechanisms, and a "fully wired," event-driven programming model rivaling that of desktop apps. Code reusability has reached a new level with user and server controls, and the underlying .NET Framework exposes a huge portion of the Windows OS. However, building a full-blown corporate Web app can still be a big leap. One way to make this leap successfully is to implement a Web portal—a Web application that provides an easily extensible interface to various types of Web-based information
Optimize Update-Concurrency Tests
Roger Jennings
Compare alternative approaches to SQL Server 2000 concurrency management. Minimizing active database connections is a primary objective of scalable data-access architectures. Maintaining a live connection to the source database while users browse, update, or insert the data behind complex business documents isn't practical for more than a few simultaneous users. ADO's disconnected Recordset objects let you maintain a local copy (snapshot) of the data of interest, disconnect, update the snapshot values, reconnect, and attempt to update the affected tables. ADO.NET's DataSet objects extend the original disconnected-recordset approach to create an in-memory representation of data tables with client-side relationship definitions, primary- and foreign-key constraints, and other data-consistency-checking features. Both object types permit offline editing by persisting the original and edited data versions to a local XML file and attempting an update after reconnecting to the network. The local-persistence scenario also applies to long-running transactions in automated business-to-business (B2B) processes, such as supply-chain management (see Additional Resources).
Cluster That Index - Part 2
Christoffer Hedgate
I have previously discussed the issue of forward-pointers in the article Cluster that index!. I described what forward-pointers are and how they are created by SQL Server. I also supplied a script that showed the effect of forward-pointers, but I did not discuss how to check for the existence of forward-pointers and how to remove them. This article will discuss this
Problem-solving blocking situations
Robert Hauck
This procedure is intended to help with problem-solving blocking situations on the server. I developed it to contact users when their client software holds locks that are holding up other users. This works faster than viewing the blocking status through Enterprise Manager. It has been tested on SQL Server 2000
How to Eliminate Collation Issues When Migrating Databases and Logins from SQL Server 7.0 to SQL Server 2000
Gregory A. Larsen
There are a number of different methods to migrate a SQL Server 7.0 database to SQL Server 2000. Before you decide which method to use, you need to know the collation differences between SQL Server 7.0 and 2000, and why these differences could lead to collation issues after migrating databases. The issues associated with the collation differences might help you decide which is the best migration method for moving your databases between SQL Server 7.0 and 2000. In addition to handling collation issues when copying databases between SQL Server versions, you will also need to consider how to migrate SQL Server logins. This article will discuss these different migration topics, as well as identifying a methodology for migrating databases to overcome the collation issues that might arise, depending on the method selected for migrating SQL Server 7.0 databases to SQL Server 2000
Connection Pooling and Connection Management
Eric Charran
In any software endeavor, the most expensive operation is that of connecting the application’s business logic and presentation code to the database. To streamline the overhead caused by these costly operations, the .NET framework natively supports connection pooling. By utilizing a specified set of connections and pooling them between multiple sessions or clients, developers can increase the scalability and extensibility of their applications. When applications or sessions share a single connection, the overhead of allocating a separate connection for each request to the database and then destroying it is eliminated
XML and the Big Three
Trudy Pelzer
XML (Extensible Markup Language) is the way to exchange data. But storing the contents of an XML document meaningfully in your database is not straightforward because XML and DBMS vendors look at the concept of data interchange differently. In this article, I'll provide a quick overview of how IBM DB2 v8, Microsoft SQL Server 2000, and Oracle9i can handle the transformation between XML and DBMS data
When managing database environments, integration is key
Ellen O'Brien
When managing complex database environments, IT vendors and buyers agree on the three top priorities: integration, integration and integration
SQL Server 2000: Merge Replication Enhancements
Microsoft Support WebCast. April 26, 2001. This document is based on the original spoken Support WebCast transcript. It has been edited for clarity. Tom Casey: Good morning everybody, and welcome to this Product Support Services WebCast. My name is Tom Casey, and I am the Product Unit Manager for Microsoft® SQL Server™ Replication. That means that I'm responsible for the development, testing, and delivery of all of the SQL Server replication technologies, both transactional and merge replication, and snapshot replication from which we derived both originally
Storage and Disaster Recovery
Welcome to the first in a series of special FTP reports. Check out these technical articles, tips, and market trends on this important topic
Web Services Land in Yukon
Darryl K. Taft
SAN FRANCISCO—Web services will play a key role in Yukon, the next version of Microsoft Corp.'s SQL Server database, a company executive said. Giving a preview of the upcoming technology at the VSLive show here, David Campbell, product unit manager of the SQL Server Engine at Microsoft, said in a keynote presentation Thursday, "SQL Server Yukon is a great Web services engine."
ISVs Courted for 'Yukon' Apps
Lisa Vaas
Microsoft Corp. plans to whet the appetite of small and midsize businesses for a major update of its SQL Server database—code-named Yukon—with a liberal helping of Yukon-ready applications
How do I fix broken logins?
Neil Boyle
When transferring a database to a new server using backup/restore or detaching and re-attaching database files, the link between server logins and database users almost invariably gets broken. Here we will take a look at why this happens, and what we can do to fix it
Fixed Database Roles
Andy Warren
In a previous article I discussed how the Public role works and some of the potential pitfalls. In this article I'd like to continue that discussion by looking at four of the fixed database roles; db_datareader, db_datawriter, db_denydatareader, and db_denydatawriter

[В начало]

ФОРУМ SQL.RU

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

Новые упражнения на http://sql.ipps.ru
Tool Вы знаете что твориться на ваших 10-30+ серверах ?
Массовые блокировки в tempdb
очень простой арифметический вопрос про НДС
Четыре SCSI-диска. Как распорядиться ?
Как сделать, чтобы триггер запустился всего ОДИН раз за всю операцию
OPENXML и большие объемы данных для парсинга.
Копирование строк
Как бы его поизящнее "размножить" записи?
И опять DBF
Naming Convention
Вечный вопрос про Microsoft Jet
Передача XML-запроса с помощью диалекта MSSQLXML
При применении снапшота пропадают большинство связей. Хелп!!!
объединение курсоров
маленькая задачка
Помогите! Раздача прав на выполнение XP
Ошибка в SQL "Invalid cursor state"
Вопрос по использованю RAM
TDS и DBASE4

[В начало]

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

Инсталяция MS SQL Server на Win2000 Server
Пример вызова DTS
Full-text default language ???
OFF: MSXML SAX и строка длинее 255 символов
Выборка без обьединения
Опять про DTS
Как поменять SET ANSI_PADDING
MS SQL Server+PHP+ Apache - хэлп
Ипользование сохраненной процедуры SQL 2000
SQL CE 2.0 + SQL 2000 SP3
Срочно нужна помощь! SQL 2000 - Wingate
Ищу фикс от Майкрософт
Фикс
Полный армагедон!!!
Как произвести обмен данными типа TEXT внутри процедуры?
Девайс для аппаратной аутентификации
Analysis Services: программный доступ к ролям
В форуме "Работа" опубликована вакансия
Взаимодействие MSSQL и IBM DB2 AS400
Вопрос про xp_sendmail
Копирование базы на ноутбук ...
Всем спасибо!!!
Опять DataGrid ???

[В начало]


Вопросы, предложения, коментарии, замечания, критику и т.п. присылайте Александру Гладченко на адрес: mssqlhelp@pisem.net

sql.ru Описание рассылки

МИНИФОРМА
ПОДПИСКИ




http://subscribe.ru/
E-mail: ask@subscribe.ru
Отписаться
Убрать рекламу

В избранное