tempdb is one of the 4 system databases that exists in all SQL Server instances. The other databases are master, model and msdb. In case of using Replication, a fifth system database named distribution will also exist. You can find all existing system databases in SQL Server Management Studio (SSMS) under the Databases / System Databases folder:
Read more »
Indexing is key to efficient query execution. Determining what indexes are needed to satisfy production needs is often a game of cat and mouse in which we are forced to react to performance problems after they are brought to our attention. Being able to proactively monitor index needs and respond effectively before complaints are received can save us immense time while preventing costly performance messes.
Read more »
Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article.
Read more »
Like any other enterprise RDBMS system, SQL Server ships with several built-in functions that make developers’ T-SQL code clean, convenient and reusable. To demonstrate the efficiency of functions, say we needed to retrieve a server name for one of our SQL Server instances. Well, one of doing this would be to write a SELECT statement that would query the system view [sys].[servers] from the master database as shown in Script 1.
Read more »
Sometimes we need to create copies of a database when we migrate a database from our testing environment to Production or maybe when we want to create a new database based on a template database.
Read more »
This is the third article of a series dedicated to discovering geographic maps in Power BI.
At the link below you can find out references to the previous articles and the project’s goal:
Read more »
We have already discussed quite some design tips for building Analysis Services (SSAS) Multidimensional cubes and dimensions:
Read more »
Introduction
This is the second article of a series dedicated to discovering geographic map tools in Power BI.
At the link below you can find out references to the previous article and the project’s goal:
Read more »
The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically.
Read more »
On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.
Read more »
As we enter an era of artificial intelligence and serverless computing, the tools to help us create perceived intelligent programs have become so advanced and intuitive that the effort in building a fully functional Q and A chat bot has become almost negligible.
In this article, I will show you how to do just that.
Read more »
Identifying the SQL Server database state and how a database can be moved between these different states is considered an important aspect of SQL Server database administration . A good understanding of this will help us in troubleshooting and fixing many database problems and issues.
Read more »
Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency. Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect. A lock as an in-memory structure is 96 bytes in size.
Read more »
Indexing is key to efficient query execution. Knowing what indexes are unneeded, incorrectly used, or unused can allow us to reduce disk usage and improve write performance at little cost to your organization.
Read more »
In any good programming reference, you will read that a developer has to document his code, not only for him/herself, but also for the person who, ten years later will be asked to maintain it. This would, of course, be made easier thanks to a good documentation of existing code.
Read more »
Implementing a new database consists of various aspects like creating indexes, tuning stored procedures or installing new RAM etc. But among all of these items, performance stress testing plays a prominent role.
Read more »
Power BI is a self-service business intelligence tool from Microsoft which has been steadily gaining momentum in the last couple of months. One of the well-known disadvantages of Power BI is that it is basically cloud only. A lot of companies are not yet at the point where they feel comfortable having their data in the cloud or are premise bound for some other reasons such as data-sensitivity, data-sovereignty or compliance.
Read more »
In the previous article in this series on dynamic management views in SQL Server, we used DMV’s to discover some basic information about databases on an instance you are connected to, along with some ideas about getting basic file system and security info. I promised to dive into security next but first I’d like to explore a topic that is currently quite critical for the company I work for today: tracking space usage.
Read more »
Just like a book index, SQL Server index has a similar purpose, to provide faster searching, gathering and sorting of information from a database, without need to go through all of the objects over and over. Instead, an index provides the gateway between the table rows and query engine, in order to achieve better both database and query performance.
Read more »
All database users know about regular aggregate functions which operate on an entire table and are used with a GROUP BY clause. But very few people use Window functions in SQL. These operate on a set of rows and return a single aggregated value for each row.
Read more »