MCITP

MCITP

Saturday, September 11, 2010

Move TempDB database to new location

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb.mdf');



GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'C:\templog.ldf');



GO


Restart the server to take the changes take place.

Wednesday, July 28, 2010

Filtered index (New in SQL server 2008) small and effective.

Ideally we can say that Filtered Index is an optimized non clustered index (Filtered Index can only be created as a non clustered index on a table), which is best suited for those queries that select a very small percentage of rows from a given table. Some of the examples of data which can be well defined subsets could be columns with NULL values or a column which has distinct range of values. Reduction in Filtered Index maintenance cost can be seen when the number of rows in the Filtered Index is very small when compared to a full table index. If the filtered index includes most of the records in the table then it will cost more to maintain a Filtered Index than a Full Table Index. So we needs to be very careful in analysis the best and useful WHERE clause which should be used in when creating a Filtered Index. Filtered Indexes basically can be created on one table and it will improve the simple comparison operation. If your application requires a filter expression which refers to many database tables or has a very complex logic then the best solution is to create a view. In SQL Server 2008 you can create 999 non clustered indexes; however this doesn’t mean that you should create as many non clustered indexes as it will create performance impact when data gets changed often within the tables.

Thursday, July 22, 2010

Example to configure Resource governer by using a template

To configure Resource Governor by using a template

  1. In SQL Server Management Studio, on the View menu, click Template Explorer.

  2. In Template Explorer, expand Resource Governor, and then double-click Configure Resource Governor.

  3. In Connect to Database Engine, enter the required information, and then click OK. The template Configure Resource Governor.sql is provided in the Query Editor. Use this template to create and configure a resource pool, a workload group, and a classifier function.

  4. To change the values in the template, press CTRL+SHIFT+M. In the Specify Values for Template Parameters window, enter the values that you want to use.

  5. To save the changes that you make to the template, click OK.

  6. To run the query, click Execute.

New DMV for memory information, Really useful.

There are five new dynamic management views to present memory information:

The sys.dm_os_sys_info dynamic management view has discontinued the cpu_ticks_in_ms column, and has added two new columns, sqlserver_start_time_ms_ticks and sqlserver_start_time.

Use compreesion in backup command


BACKUP DATABASE
AdventureWorks
TO DISK = 'C:\SQLData\AdventureWorks_compressed.bak'
WITH COMPRESSION, INIT, STATS = 10

***************************
It compresses data up to 75% and reduction in time as well

Now time has come to remove backup pro and lite speed.

Restore doesn't need any extra command.
***************************

New features in SQL server 2008

Backup Compression:

This is introduced in Enterprise or above editions. Now sql server supports compressing backups. Compressed backup can be restored to any edition of sql server 2008.

Configuration Servers:

This is a new method of administering multiple servers. An instance of sql server is designated as a configuration server to maintain list of registered servers.

Change Tracking:

SQL Server now allows applications to obtain incremental changes to user tables by tracking changes, which enables developing synchronization applications is easier and faster.

FILESTREAM storage:

FILESTREAM in SQL Server 2008 enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data.

Hot Add CPU:

SQL Server 2008 supports dynamically adding CPUs to a running system. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer.

Plan Guide Enhancements:

The sp_create_plan_guide stored procedure has been extended to accept XML Showplan output directly in the @hints parameter instead of embedding the output in the USE PLAN hint. This simplifies the process of applying a fixed query plan as a plan guide hint. You can create multiple OBJECT or SQL plan guides for the same query and batch or module. However, only one of these plan guides can be enabled at any given time.

Query Processing on Partitioned Objects:

SQL Server 2008 improves query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhances the partitioning information provided in both compile-time and run-time execution plans.

Partition Switching on Partitioned Tables and Indexes:

Partitioning data enables you to manage and access subsets of your data quickly and efficiently while maintaining the integrity of the entire data collection. Now you can use partition switching to quickly and efficiently transfer subsets of your data by switching a partition from one table to another.

Resource Governor:

Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to limit the amount of CPU and memory that incoming application requests can use.

Extensible Key Management:

The Extensible Key Management (EKM) feature in the Enterprise, Developer, and Evaluation Editions of SQL Server 2008 allows third-party enterprise key management and hardware security module (HSM) vendors to register their devices in SQL Server.

Transparent Data Encryption:

Transparent Data Encryption introduces a new database option that encrypts the database files automatically, without needing to alter any applications.

Server Administration:

SQL Server 2008 introduces Declarative Management, a new policy-based management framework for the SQL Server Database Engine.

Spatial Data Storage, Methods and Indexing:

Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes.

SQL Server Management Studio Enhancements:

Transact-SQL Query Editor IntelliSense:

The Transact-SQL Editor now provides IntelliSense functionality such as word completion and error underlining. IntelliSense is provided for frequently used Transact-SQL elements.

Transact-SQL Error List Window:

SQL Server Management Studio includes an Error List window that displays the syntax and semantic errors generated from the IntelliSense code in the Transact-SQL Query Editor.

Tools Enhancements:

SQL Server Management Studio has the following improvements:

® In the Query Editor window, you can query multiple servers at the same time by opening query windows from registered server groups. The query results can be combined into a single results pane, or can be returned in separate results panes.

® You can access SQL Server Profiler from the Query Editor window, from the Query menu, the Query Editor toolbar, or by pressing CTRL+ALT+P.

® You can now open tables by using a Return Top n rows option.

® On the Tools/Option menu, you can specify the action that results by double-clicking tables in Object Explorer.

Transact-SQL Enhancements:

Lock Escalation Option - A new LOCK_ESCALATION option of ALTER TABLE allows you to disable lock escalation on a table.

Query Hints - Table hints can now be specified as query hints to provide advanced query performance tuning options.

hierarchyid Data Type - SQL Server 2008 introduces a new system-provided data type to encapsulate hierarchical relationships.

MERGE Statement - This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.

Table-Valued Parameters - The Database Engine introduces a new parameter type that can reference user-defined table types.

User-Defined Table Type - The Database Engine introduces a new user-defined table type that supports representing table structures for use as parameters in stored procedures and functions, or in a batch or the body of a stored procedure or function.

Compatibility Level - ALTER DATABASE SET COMPATIBILITY_LEVEL replaces sp_dbcmptlevel for setting the database compatibility level.

GROUPING SETS - The GROUPING SETS, ROLLUP, and CUBE operators are added to the GROUP BY clause.

Table Hints - The FORCESEEK table hint is added to provide advanced query performance tuning options.

Transact-SQL Row Constructors - Transact-SQL is enhanced to allow multiple value inserts within a single INSERT statement.

Data Collector:

SQL Server 2008 introduces a data collector that you can use to obtain and save data that is gathered from several sources.

Database Mirroring:

Log Performance Enhancements:

® Compression of stream data for which at least a 12.5 percent compression ratio can be achieved.

® Write-ahead on the incoming log stream on the mirror server.

® Improved use of log send buffers.

® Page read-ahead during the undo phase.

Automatic Recovery from Corrupted Pages:

A database mirroring partner running on SQL Server 2008 or later versions automatically tries to resolve certain types of errors that prevent reading a data page.

SQL Dependency Reporting Enhancements:

SQL Server 2008 introduces a new catalog view and system functions to provide consistent and reliable SQL dependency reporting.

SQL Server Extended Events:

SQL Server 2008 introduces SQL Server Extended Events, an event infrastructure for server systems.

Change Data Capture:

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

Optimized Bitmap Filtering:

The query optimizer can place bitmap filters dynamically in parallel query plans to improve the performance of queries against a star schema.

Dynamic Management Views:

There are five new dynamic management views to present memory information.

DDL Triggers and Event Notifications:

The class of events on which you can create DDL triggers and event notifications is expanded to include numerous stored procedures that perform DDL-like operations.

Hey ,

Beleive me, you guys are going to have fun and learning here.