What is backup and restore in SQL Server disaster recovery? April 16, 2014 by Ivan Stankovic Defining SQL Server backup and restore The SQL Server backup and restore technique involves steps for creating backups, restoring databases, and saving a valid backup copy in a different location. It is a built in SQL Server feature, useful for disaster recovery plans. Read more »
Poor database indexing – a SQL query performance killer – recommendations April 14, 2014 by Milena Petrovic In the previous article, we explained what clustered and nonclustered indexes were, and showed how to create them. In this article, we will explain what poor indexing is and give general recommendations for table indexing and selecting the right columns to create indexes on. Read more »
Frequent query recompilations – a SQL query performance killer – detection April 11, 2014 by Milena Petrovic In the previous part of this article, we presented query recompilation basics. We explained when a query is compiled, when recompiled, and what preferred behavior is. We also showed the how to trigger a recompilation using T-SQL and provided examples. In this part, we will focus on detecting frequent query recompilations and frequently recompiled queries. Read more »
Frequent query recompilations – a SQL query performance killer – introduction April 9, 2014 by Milena Petrovic When it comes to SQL Server performance tuning, there are many factors that should be checked. One of the top performance killers is a frequent query recompilation. It adds pressure to the processor and degrades performance. Read more »
Creating and using CRUD stored procedures April 7, 2014 by Milica Medic A typical N-tier application design contains of the several layers from the client (Web or Windows), to the Business Logic Layer, to the Data Access Layer, and finally to the Data Storage Layer. Read more »
Database table partitioning in SQL Server April 4, 2014 by Milica Medic What is a database table partitioning? Partitioning is the database process where very large tables are divided into multiple smaller parts. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan. The main of goal of partitioning is to aid in maintenance of large tables and to reduce the overall response time to read and load data for particular SQL operations. Read more »
Common SQL syntax errors and how to resolve them April 2, 2014 by Milena Petrovic In the SQL Server Management Studio, errors can be tracked down easily, using the built in Error List pane. This pane can be activated in the View menu, or by using shortcuts Ctrl+\ and Ctrl+E Read more »
SQL query performance killers – understanding poor database indexing March 31, 2014 by Milena Petrovic SQL Server performance is affected by many factors. The most common SQL Server performance killers are poor database design, poor indexing, poor query design, not reusable execution plans, frequent query recompilation, excessive fragmentation, and more. With these factors present on your machine, even adding additional hardware resources might not help, as these SQL Server performance killers can use all available resources. Read more »
SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances March 28, 2014 by Ivan Stankovic In this series of articles (see below for links to other articles) about the SQL Server Policy Based Management feature, we have explained concepts, terms, basic, and advanced tasks that create complex conditions and policies. Read more »
SQL Server security and Policy Based Management – Advanced conditions March 26, 2014 by Ivan Stankovic Although Policy Based Management in SQL Server provides a variety of facets with a range of properties and values in order to create complex conditions, DBAs often require more. Every SQL Server environment is specific and different security policies may be required. Read more »
SQL Server security and Policy Based Management – Alerting March 24, 2014 by Ivan Stankovic In the previous article related to the SQL Server Policy Based Management feature, we discussed and provided steps and tips to create conditions and policies that help maintaining SQL Server security in large enterprises with a number of SQL Server instances. Now, we are going to go further as once set policies are not much of a use if their violations are left unnoticed. Read more »
SQL Server query execution plans – Examples with the WHERE clause March 21, 2014 by Milena Petrovic In the previous part of this article, we explained how indexes affect SQL Server query execution performance and plans, and provided some basic examples. In this part, we will continue with examples that show how indexes affect query execution plans and costs. Read more »
SQL Server query execution plans – Examples with the SELECT statement March 19, 2014 by Milena Petrovic SQL Server query execution plans are very useful in query performance analysis and troubleshooting. There are two types of query execution plans in SQL Server: actual and estimated. They show how a query was executed and how it will be executed Read more »
SQL Server query execution plans – Understanding and reading the plans March 17, 2014 by Milena Petrovic In the previous parts of this series, we explained what SQL Server query execution plans were, why they should be used, and presented various methods to show them. In this part, we will explain the symbols used, how to read the plans, and how to use these plans in performance analysis and troubleshooting Read more »
Using an XML file to configure an SSIS package March 16, 2014 by Marko Radakovic To configure an SSIS package means to choose property/value pairs added to a completed package, and include them in the configuration file for the further modifications. If for any reason some of the values has been changed after the deployment is finished and the package run, they can be added later, opening the package in Business Intelligence Development Studio (BIDS), and changing the values Read more »
SQL Database design: Choosing a primary key March 16, 2014 by Milica Medic A primary key is a column or a combination of columns that uniquely defines each row in a table. When designing a database choosing a primary key is one of the most important steps. A table can contain one or more columns suitable to be a primary key and that column or columns are called a candidate key. Only one candidate key can be selected as a primary key Read more »
What is SQL Server log shipping? March 16, 2014 by Ivan Stankovic What is SQL Server log shipping? SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations Read more »
SQL Server security and Policy Based Management – In practice March 16, 2014 by Ivan Stankovic In the previous introduction article we have described concepts, terms, and common Policy Based Management tasks. The SQL Server Policy Based Management feature can be used in various scenarios and for different purposes. Using once declared policies and the Export/Import feature, maintaining SQL Server security in large enterprises with a number of SQL Server instances can be an automated process Read more »
SQL Server security and Policy Based Management – Introduction March 16, 2014 by Ivan Stankovic The Policy Based Management feature was introduced in SQL Server 2008. The purpose of the feature is to assists SQL Server administrators in creating and enforcing policies tied to SQL Server instances and their objects. The policies can be configured on one SQL Server and re-used on other SQL Server instances to provide a SQL Server security model for instance Read more »
Using SSIS packages to import MS Excel data into a database March 15, 2014 by Marko Radakovic Inserting the data in the database table can be hard and can take a long time. Especially today when the databases becoming bigger and bigger. Automated data importing is possible using different methods, depending on source file type and data organization inside Read more »