Major Points when Creating Indexes for a database

Best practices to follow when creating Indexes for a database. Examine the WHERE clause and JOIN criteria columns : SQL server Query optimizers looks for indexes on the columns specified in the WHERE clause and the JOIN criteria and if the indexes are available on these columns then optimizer considers Indexes when creating execution plan. Use Narrow Indexes : index on a column with narrow data type will improve performance by reducing storage space, reducing required IO and more effective caching. Prefer Column with high selectivity : Index on a column with a very low range of unique value will not yield any benefit in … Click here to continue…..

How to increase SQL Server performance

These are major problem that badly affects SQL Server performance. If you already know this problem area then you can design database and queries with better understanding. Poor Indexing: Without proper indexing SQL Server needs to retrieve and process much more data when executing. That means more use of disk,memory and CPU, and also increased execution time. That can lead to bloacking and deadlocks. Inaccurate Statistics: Accurate data distribution statistics are important for SQL Server so that it can design optimized execution plan accordingly. Poor Query Design: SQL queries should be written to make best use of indexes.Poorly designed queries can … Click here to continue…..

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL  Statements. With a linked server, you can create very clean, easy to  follow, SQL statements that allow remote data to be retrieved, joined  and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What are the basic functions of master, msdb, model, tempdb and resource databases in SQL Server?

The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care. The msdb stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping. The tempdb holds temporary objects such as global and local temporary tables and stored procedures. The model is essentially a template database used in the creation of any new user database created in the instance. The resoure is … Click here to continue…..

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display … Click here to continue…..