Log in

CodeSource [entries|archive|friends|userinfo]

[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

Clustered vs NonClustered Indexes [Mar. 30th, 2006|01:15 pm]

Great read, too long to post here!
linkpost comment

Wrap and Shrink Database Log Files (Script) [Mar. 27th, 2006|11:00 am]

DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

-- Your criteria here.
USE [DATABASE NAME] -- This is the name of the database for which the log will be shrunk.
SELECT @LogicalFileName = 'log', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- in MB

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
(DummyColumn char (8000) not null)

-- Wrap log and truncate it.
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

use master
EXEC (@TruncLog)

-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
use master
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
PRINT '*** Perform a full database backup ***'
linkpost comment

Schema Clarification (SQL 2005) [Mar. 3rd, 2006|03:08 pm]


FROM: http://www.databasejournal.com/features/mssql/article.php/3481751

February 11, 2005
SQL Server 2005 Security - Part 2 Authorization
By Marcin Policht

Following the discussion of new or enhanced authentication-related functionality in SQL Server 2005 Beta 2 in our previous article, we are shifting our focus to authorization features, which determine the level of access rights once the user's logon process is successfully completed. Among topics that will be covered here, are separation of user and schema, modifiable context of module execution, increased permission granularity, and improved catalog security.

User-schema separation - this feature tends to be a bit confusing due to a lack of clarity regarding the concept of "schema" in earlier versions of SQL Server. In essence, schema can be thought of as a container where individual database objects reside. Characteristics of this container satisfy two main rules, defined by ANSI SQL-92 standards. According to the first rule, schema forms the namespace boundaries, which means that no two objects within it can be assigned the same name. This is because for every object, its fully qualified name, consists of four parts - ServerName.DatabaseName.SchemaName.ObjectName - must be unique. The second rule states that all objects within the schema have the same owner.

In SQL Server 2000, the schema name was equivalent to the database user who owned it, which means that the fully qualified name of every object was inherently tied to its owner, which means that you could create two identically named objects in a SQL Server 2000 database, as long as they had different owners. Such dependency had a number of undesired side effects. In particular, deleting a user required either dropping all objects in this user's possession or reassigning their ownership. This, in turn, had the potential of breaking any application that included hard-coded references to fully qualified object names. In SQL Server 2005, the schema is no longer equivalent to the name of the database user, which eliminates this problem. Furthermore, schema can be owned not only by a single user but also database roles or Windows groups. This allows for shared ownership of objects or the creation of application-specific schemas, which simplifies their development and maintenance, since you can refer to their objects using consistent schema names.

Each SQL Server 2005 database contains a number of automatically created schemas. Four of them correspond to pre-defined database users (dbo, guest, INFORMATION_SCHEMA, and sys) and the remaining ones correspond to fixed database roles (db_owner, db_accessadmin, db_securityadmin, db_dlladmin, db_backupoperator, db_datareader, db_datawriter, db_denydatareader, and db_denydatawriter). You can view a list of schemas along with the principals associated with them, by examining the content of the sys.schemas catalog view and cross-referencing it with the sys.database_principals catalog view. In addition, SQL Server 2005 provides the ability to assign a new "default schema" (other than the initial dbo default) on a per database user-basis, using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER T-SQL statements. When attempting to resolve object names specified in a non-fully qualified format, SQL Server 2005 will use the default immediately following a check of the sys schema (this takes priority regardless of what the default has been set to). Assigning a common default schema for a number of database users offers the benefit of a common name resolution mechanism (useful for all users of the same application). Creation of a new schema is accomplished by executing the CREATE SCHEMA T-SQL statement, with the AUTHORIZATION clause designating the user or role as its owner (the ALTER SCHEMA statement can be used to alter the ownership).

Module execution context - to fully understand the benefits of this feature, let's first take a look how the execution context of programmable modules (such as stored procedures or functions) on dependent objects was handled in SQL Server 2000. The ability to launch such modules was (and still is, in SQL Server 2005) determined by their permissions (for example, a user would need permissions to execute a specific stored procedure in order to do so). If their execution required access to another object (a stored procedure that reads or modifies a table), then an evaluation of the permissions necessary to accomplish this would depend on whether both the module and the object have the same owner. This mechanism is called ownership chaining and still applies in SQL Server 2005. If this were the case, then no additional checks would be performed. Otherwise, (whenever the module and the object belong to two different users), explicit permissions to that object would be required as well. However, in either case, all actions performed as part of the module execution would take place in the security context of the user who invoked it. Additionally, ownership chaining has its limitations, since it applies only to a data modification language statement, not to a data definition language statement or dynamic queries included in programmable modules.

SQL Server 2005 provides the ability to alter the execution context with the EXECUTE AS clause available as part of the definition of stored procedures, functions, queues, and triggers. This affects which user account is used to evaluate permissions required by objects referenced by the running module. The caller, however, still requires permissions to invoke the module. Execution context information is maintained in the sys.sql_modules catalog view. SQL Server 2005 Beta 2 allows the following values to be used as part of the EXECUTE AS clause:
o EXECUTE AS CALLER - the default value, matching the SQL Server 2000 behavior described above, where the module executes in the security context of its caller. In this case, if access to any object is needed, it is evaluated based on the standard ownership chaining mechanism. However, you should keep in mind (as we already mentioned in the User-schema separation section) that in SQL Server 2005, objects are not owned directly by users, but instead are part of schemas, which, in turn, have users or roles assigned to them.
This value can be applied to functions, stored procedures, both data modification language and data definition language (introduced in SQL Server 2005) triggers (with database and server scopes), as well as in Service Broker queues.
o EXECUTE AS USER='user_name' (or 'login_name') - assigns the security context of a specific database user for the duration of the module execution. This means that permissions on all referenced objects are evaluated against this user_name, rather than against the original caller (regardless of whether the module and objects have the same ownership or not). This value can be applied to functions, stored procedures, both data modification language and data definition language (introduced in SQL Server 2005) triggers with database scope, as well as queues. 'login_name' (designating a valid SQL Server 2005 login) is applicable to data definition language triggers with server scope. Note that in order for the module to work as intended, its creator needs to either be a member of SysAdmin server role, db_owner database role, or have Impresonate permissions on the user account whose security context is used.
o EXECUTE AS SELF - designates the user who creates (or modifies) the module definition (not necessarily the same as its owner) as the one whose security context will be used to execute it (useful in scenarios where applications create modules for its users on demand). The Principal ID of this user is stored as part of the metadata (in the execute_as_principal_id column in the sys.sql_modules catalog view) and is referenced during module execution. This value can be applied to functions, stored procedures, both data modification language and data definition language triggers (with database and server scopes), as well as in queues.
o EXECUTE AS OWNER - designates the owner of the module (or rather the module's schema) as the one whose security context will be used to execute it (note that in this case, the owner must be an individual user, not a Windows group or database role). As with the previous option, the Principal ID value is stored in the execute_as_principal_id column in the sys.sql_modules catalog view and used subsequently during module execution. This value can be applied to functions, stored procedures, and data modification language triggers with database scope.

More granular permissions - SQL Server 2005 still supports all of the fixed server and database roles introduced in SQL Server 2000, along with their default permissions. However, custom database roles, application roles, as well as server logins and database users can be granted (or denied) more granular permissions on a much wider range of objects. All securable objects (access to which is controllable via permissions) have been grouped into three scopes - server, database, and schema (each of which can also be secured). As in earlier versions of SQL Server, server level permissions are applicable to logins, while database level permissions can be assigned to users, custom database roles, and application roles. Server level permissions are stored in the sys.server_permissions catalog view, and database related ones reside in sys.database_permissions catalog view.

Permissions can be assigned one of three states - Grant, Deny, and Revoke (which removes existing Grant or Deny permissions). Permissions applied on higher levels imply the same states on lower levels, unless Deny (which always takes precedence) is explicitly used. Increased granularity simplifies implementing the rule of the least privilege (by allowing delegating individual tasks without granting membership in privileged server or database roles). Some of the most commonly used permission types present in earlier version of SQL Server, such as EXECUTE, SELECT, or TAKE OWNERSHIP offer new possibilities in their new implementation (since they can be applied on different levels and offer inheritance capabilities). There are also others, newly introduced ones, including, for example:
o CONTROL - functionally equivalent to all permissions granted to the object's owner and inherited by all subentities within its scope,
o ALTER - provides the ability to alter properties of an object. Depending on the scope, you can limit its inheritance to objects of a specific type (for example, its variation in the form ALTER ANY 'object_type' grants permissions to modify every instance of 'object_type' within server or database scope). ALTER TRACE allows running Profiler without membership in SysAdmin fixed server role.
o IMPERSONATE - as mentioned earlier, permits impersonating another user (without requiring SysAdmin or dbo privileges, as was the case in SQL Server 2000),
o VIEW DEFINITION - gives read access to an object's metadata via catalog views.

Improved catalog security - direct access to system tables is no longer allowed - instead, they are exposed through catalog views, encompassing both server and database-wide settings. Their permissions are set on the row level, with minimal access granted to public role. Visibility of objects via catalog views is limited to owners or users with permissions granted on them (as mentioned above, it is also possible to grant read access to catalog views via VIEW DEFINITION permission). In addition to catalog views, you can still use ANSI INFORMATION_SCHEMA views to review their content (although this requires the same types of permissions). Catalog views can be accessed either via graphical interface (they are listed in the System Views subfolder of the Views folder for each database in the SQL Server Management Studio) or by referencing sys.system_views via a T-SQL statement.

In the next installment of our series, we will present the remaining security-related features in SQL Server 2005 Beta 2.
linkpost comment

Database Fragmentation [Feb. 9th, 2006|11:01 am]


Snipets from SQL Server Magazine: February 2006
Article: Winning the Fragmentation Battle

Hard Disks (and their role)
Drives dedicated to holding SQL Server's databases will never have fewer than 8196 bytes per transfer, but what you want is a consistent 65,536 (or more) bytes per transfer (65,536 bytes, or 64K, equal one extent). If you see a value that's less than 65,536 as an average, fragmentation is likely to be a problem.

If Performance Monitor shows excessive I/Os but FileMon shows at least 65,536 bytes per I/O it means the database file itself is fragmented on the physical disk. Excessive use of DBCC SHRINKDB can cause this.
(Partial Solution: Use Disk Defrag or a similar tool)
(Complete Solution: Backup the dataase, drop the database, defrag the disk and restore the database from the backup)
(Preemptive Solution: allocate enough space to allow for growth when you first create the database. This will ensure that the database file is consistent across the physical disk.)

Because the clustered index orders the data physically on the disk according to the index columns, fragmentation usually occurs when data has been deleted from a table but no data has been inserted that would fill the empty space. A common reason for this type of fragmentation is having a clustered index on a monotonically increasing primary key, for example an identity column.

Another common way for both clustered and nonclustered indexes to become fragmented is through allocations of extents over time. In an online transaction processing (OLTP) environment, typically new rows are added a few at a time, not in large blocks,; there fore, all the tables in a file group will be allocating extents one or two at a time resulting in scattered extents throughout the file.

DBCC SHOWCONTIG - analysis can slow performance, so only use off hours if performance is already an issue
DBCC SHOWCONTIG FAST - less invasive but can still slow performance

NOTE: This entry will be updated with useful tidbits as I read further.
linkpost comment

Removing Duplicates in SQLServer 2005 [Feb. 7th, 2006|10:04 am]


Recently I read Muthusamy Anantha Kumar's article "Row_NUMBER() function in SQL Server 2005" (http://www.sqlservercentral.com/articles/articlelink.asp?articleid=2239) article. In it, he explored not only the new ROW_NUMBER() function, but, also gave a good example of SQL Server 2005's common table expression.

When I saw his sample, I immediately realized that I could use this to solve one of my more perplexing problems in SQL Server - how to reduce duplicates in a record set to a single row. I will demonstrate how to "de-dupify" a record set in SQL Server 2005 in a single query - a feat I would find myself using a cursor for in earlier versions of SQL Server. Particularly vexing were issues where you didn't just have two copies of a record but might have three or more copies or ragged numbers - two here and three there. My solution below accounts for this hardest case of the ragged numbers of duplicates.

I slightly modified Muthusamy's original example.

Read MoreCollapse )

From: http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp
linkpost comment

Partitioned Tables & Indexes in SQL 2005 [Feb. 3rd, 2006|04:07 pm]


Body too large to paste here...

From: http://msdn.microsoft.com/SQL/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5partition.asp
linkpost comment

Upgrading to SQL Server 2005 [Feb. 3rd, 2006|01:58 pm]


The Upgrade Process
Before we look at the upgrade considerations for each major SQL Server 2005 component, let’s review a general road map that can help your upgrade team from planning to production. You can break the upgrade process into four phases: planning and research, testing and process validation, the production upgrade, and post-upgrade considerations.

Read MoreCollapse )

From: http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx#EFAA
linkpost comment

Introduction to SQL Server 2005 [Feb. 2nd, 2006|11:56 am]


1. Introduction
Each successive release of SQL Server has given developers new and enhanced tools for writing better Transact-SQL (T-SQL) code and SQL Server 2005 is certainly no exception. It brings many improvements for the developer, from incorporation of common language runtime (CLR) coding to some simple yet powerful enhancements to the T-SQL language.

The document covers the following contexts or features of SQL Server 2005.

. SQL Management Studio.
. New features added for TSQL.
. Integration of the .NET Common Language Runtime (CLR).

Let's discuss each of the above features in a greater detail.Collapse )

From: http://www.sqlservercentral.com/columnists/fFinney/introductiontosqlserver2005.asp
linkpost comment

[ viewing | most recent entries ]