Wednesday, April 16, 2014
 
  SQL Server Tags
 
 
     

Pagesplits are Killers and Killers are Pagesplits

Say you have a glass filled with water and you try to put more water in that glass, what happens? Water in the glass will overflow. Exactly the same way, whenever a new row is added to a full index page, SQL Server moves around half of the rows to a new page to make room for the new row. This is known as PAGE SPLIT. Page splits can make room for new records but can be very resource intensive. Page-Splits can also incur fragmentation which may adversely affect I/O operations.

Remotely Executing Stored Procedures, Functions, Queries and Commands Using EXECUTE

We use EXEC or EXECUTE command almost every day of our database life. It’s a command that allows us execute Stored Procedures (extended, system, user, CLR, etc.)  and Functions. However, EXEC command also has a powerful feature that allows you to run any command remotely. Not many developers are aware of this feature. Historically, EXEC/EXECUTE executed commands locally, meaning if you have a SPROC you can only run that on your local instance of SQL Server. That’s changed now.

Trigger-Less Triggers Using OUTPUT Clause

Feb 24 2014
0
0

You can now audit your changes using DML statements without even using TRIGGERS:

 

SQL Server supports an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the result set in a table or table variable.

YOUR SA PASSWORD FOR SQL SERVER 2012 IS LOST! NOW WHAT?

Nov 08 2012
5403
190

Author: Saleem Hakani (Microsoft Corporation)

You are a proud and a trusted DBA of your organization who is responsible for some important services running on SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access:

SQL SERVER INDEXING OPERATIONS ON STEROIDS

Jul 18 2012
377
29

Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time?

There are many things that happen behind the scenes that could contribute to the slowness of index operations.

Some of the reasons include:

1. Load on SQL Server,

2. Out of date statistics,

3. Server configuration setting for degree of parallelism,

4. Amount of available memory

5. Amount of available resources,

6. etc.

SQL Server can intelligently detect the load on the server and auto-adjusts the amount of resources that can be allocated to a process. In the case of Index operations; if SQL Server is busy, it will automatically adjust the Max Degree of Parallelism server configuration setting to accommodate the process or the load. This means it could either grant or limit the number of CPU’s Index operations can use. In many cases this is the best practice and you should not change the default value.

INTRODUCING SEQUENCE IN SQL SERVER 2012

May 07 2012
404
57

Author: Saleem Hakani (Microsoft Corporation)

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we’ve been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. You’ve tried IDENTITY() and now try the new feature added in SQL Server 2012 and experience the difference.

SQL Server 2012 introduces a brand new schema bound object called SEQUENCE. Sequence generates numeric values based on the specification of a SEQUENCE object. You can generate numeric values in either ascending or descending order and they can be independent of tables unlike IDENTITY columns.

INTRODUCING OFFSET & FETCH QUERY OPTIONS

Jan 28 2012
6268
241

Author: Saleem Hakani (Microsoft Corporation)

SQL Server 2012 introduces a brand new query hints that allow you to implement query paging solution. In the past, we have used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.

 

BEST PRACTICES - LOCKING DOWN SA ACCOUNT IN SQL SERVER

Jan 25 2012
3130
358
Many of the services companies run within a windows domain network uses Windows Authentication. However, there are many companies that still use Mixed Authentication. If you are allowing SQL Server standard logins for access to your SQL Server, then you must ensure that your SA account is locked down as completely as possible and that only key people in the team know the password.

INTRODUCING IIF() TO SQL SERVER FAMILY

Jan 23 2012
485
23
Author: Saleem Hakani

IIF() function is new to SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.
  
HOME   |   Article Library   |   Scripts & Tools   |   Training Videos   |   Job Board   |   Resources
Copyright 2010 by SQLCOMMUNITY.COM