Functions vs stored procedures in SQL Server
February 14, 2017Introduction Usually DBAs prefer stored procedures in SQL instead of functions in SQL Server. Is this a good practice?
Search Results for: performance
SQL Server Database Recovery Process Internals – database STARTUP Command
February 14, 2017A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on lot of scenarios. The desire to improve recovery results has resulted in various procedures, but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping […]
Introducing SQL Server Incremental Statistics for Partitioned Tables
February 3, 2017If you are maintaining a very large database, you might be well aware of the pain to perform update statistics on a very large table. This article introduces incremental statistics which is available from SQL Server 2014 highly simplifies statistics management on very large partitioned tables.
How to install, configure and use SQL Server Distributed Replay
February 1, 2017Introduction The Microsoft SQL Server Distributed Replay feature has been provided by Microsoft since SQL Server 2012 and it is used to replay captured traces against an upgraded test environment.
SQL Server database continuous integration (CI) Best practices and how to implement them – Testing, processing and automation
January 31, 2017Testing Test databases should be processed with unit tests In many shops code is unit tested at the point of commit. For databases, I prefer running all unit tests at once and in sequence against a QA database, vs development, as part of a Test step, in my continuous integration workflow pipeline. Yes, issues would […]
Indexing SQL Server temp tables
January 31, 2017SQL Server temp tables are a special type of tables that are written to the TempDB database and act like regular tables, providing a suitable workplace for intermediate data processing before saving the result to a regular table, as it can live only for the age of the database connection.
Are SQL Server database triggers evil?
January 25, 2017Introduction There is a lot of talk about how bad triggers are, how you should never use them, etc. I wanted to spend some time reviewing fact vs fiction and do an objective analysis of SQL Server database triggers (both DDL and DML), warts and all. We will review alternatives and compare them with triggers […]
Using Memory-Optimized Tables to Replace SQL Temp Tables and Table Variables
January 24, 2017TempDB usage can be considered as a performance bottleneck for workloads that use SQL temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory […]
Best Practices for Configuring Newly Installed SQL Server Instances
January 20, 2017Often we install SQL Server on clients or we get to clients where they have pre-installed SQL Server Instances. As a DBA we have the Primary responsibility of keeping the Database Up and Running. This responsibility leads to optimizing, performance tuning and many other stuff related to database’s internal objects. While keeping the other aspects […]
Using sp_server_diagnostics
January 19, 2017Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us […]
Scaling out Reporting Services & changes in SQL Server 2016
January 18, 2017Scaling out Reporting services to multiple nodes, in itself, is a relatively simple process. It’s when we come to solve problems and investigate performance that we begin to see there is a lot more going on under the hood that’s not clear through implementation. There are also some foundational elements that have changed in SQL […]
Deploying Packages to SQL Server Integration Services Catalog (SSISDB)
January 16, 2017Starting with SQL Server 2012, Integration Services (SSIS) packages can now be deployed to a single source for managing execution in multiple environments. The SSIS Catalog is a single database container for all deployed packages. The configuration files are replaced with Environments. Deployed versions are tracked historically and a package can be reverted to a […]
Automate Delivery of SQL Server Production Data Environments Using Containers
January 12, 2017There has been a lot of buzz about Docker and containers this year, and earlier this Fall Microsoft released container support in Windows Server 2016. WinDocks is a team of former Microsoft engineers, and we released an independent port of Docker’s open source in March of 2016. Full disclosure, I am a co-founder at WinDocks. […]
How to download and install the SQL Server Database Experimentation Assistant (DEA)
January 12, 2017Introduction The Database Experimentation Assistant (DEA) is a new Experimental solution provided by Microsoft used to help upgrading databases from older SQL Server versions (MSSQL 2005 or higher versions) to the more recent ones (MSSQL 2012 or higher versions). It accomplished this based in a given workload that is previously captured from the source database […]
SQL Server 2016 Always On Availability Group with Direct Seeding
January 12, 2017SQL Server Always On Availability Groups are an enterprise-level high-availability and disaster-recovery feature introduced the first time in SQL Server 2012as an alternative to database mirroring. A set of user databases that fail over together forms the availability group. These availability databases are hosted by the availability replicas and can be readable- writable at the […]
10 SSMS Tips and Tricks to boost your Productivity
January 6, 2017I have been using SSMS for a long time now, and over the period of time I have realized that it’s a very powerful IDE – however, there are lots of hidden features which are less known to lots of developers. In this article, I will be sharing some beneficial Tips and Tricks which will […]
SQL Server Index vs Statistics a consultants woes…or rants
December 29, 2016As a DBA, I am often asked why is something performing slow, what and why statistics need to be updated or what will cause them to be “off”. My initial question to clients when they pose these questions to me is what changed on your end? Did the data change significantly and did the rebuild […]
SQL Server Estimated Vs Actual Execution Plans
December 29, 2016A SQL Server execution plan is the most efficient and least cost road map that is generated by the Query Optimizer’s algorithms calculations to execute the submitted T-SQL query. Execution plans are used by the database administrators to troubleshoot the performance of poorly performing queries to isolate the part of the query that is at […]
Java HashCode in TSQL
December 28, 2016The Java HashCode method is used to determine uniqueness or similarity of strings. While implemented in Java, there can be many benefits of creating a similar or customized version of this method.
Why is my CTE so slow?
December 22, 2016Introduction Have you ever written up a complex query using Common Table Expressions (CTEs) only to be disappointed by the performance? Have you been blaming the CTE? This article looks at that problem to show that it is a little deeper than a particular syntax choice and offers some tips on how to improve performance.
New features and enhancements in SQL Server 2016 SP1
December 20, 2016SQL Server 2016 SP1 is released as announced by Microsoft. It comes with a bunch of new features and enhancements as a result of customer and community feedback. In this article, I will introduce you to these new features and enhancements.
Top 5 New SQL Server 2016 DMVs for DBAs
December 20, 2016Since its initial release that was part of SQL Server 2005, Dynamic Management Views (DMV) changed – mostly for the better – the role of database administration (DBA) within SQL Server-based environments. They improved the efficiency of DBAs regarding database server monitoring, issue diagnoses, and subsequent performance optimisation. As a result of this positive reception, […]
Point in Time Recovery with SQL Server
December 19, 2016Introduction How often are you working with multiple environments? For example, if you are a database administrator who is responsible for a production environment as well as another environment, it most likely that you will be working with both environments simultaneously. What is the probability that you will execute a script on production, which actually […]
Introduction to SQL Server Analysis Services (SSAS) monitoring
December 15, 2016When it comes to monitoring of SQL Server Analysis Services (SSAS) performance, as it relates to the database engine, there are several categories that should be mentioned and compared.
Microsoft SQL Server database deployment process
December 14, 2016Introduction Database deployments are critical tasks that can affect negative in on performance in production. In this article we’ll describe some performance related best practices for database deployments.