Every developer knows the Logical Operators such as Inner Join, Left Outer, Right Outer Join etc in SQL Server.
When you are asked to define the term data warehouse, You will say that it is a framework that will be used to analyse enterprise-level data If it is a framework, data warehouse user should have the option of using the data warehouse irrespective of the language that he is familiar with.
This new article brings you how to analyse your data using multiple languages. This article explains, what are the modifications that you need to do for the SQL Server stack to incorporate multi-language into the data warehouse.
There are multiple articles on SQL Server Analysis Service (SSAS).
Please find the latest article at SQLShack in this link.
TempDB database plays a major role in SQL Server. Therefore, it is extremely important to monitor the health of the TempDB database. One of the major challenges in TempDB is maintaining it's log file. If there are transactions that use the TempDB and if those are long-running transactions, there can be situations where the log file will grow. Since these transactions are not closing, log space will not be returned and the entire server will not be able to run queries that use the TempDB.
Recently, one of the Clients had a similar problem. One query was running for more than four days and it had consumed TempDB log file. This has caused empty disk space and the entire server is halted for operations.
In this situation, the easiest and laziest thing to do is the restart the server. Restart will kill all the transactions and return TempDB back to the original size. This is not something that you can do for a system of 24x7.
However, we choose not to restart but to identify the long-running query from the following simple query.
SELECT se_tr.session_id,
sec.login_name,
trn.database_transaction_begin_lsn,
trn.database_transaction_begin_time,
trn.database_transaction_log_record_count,
trn.database_transaction_log_bytes_used,
trn.database_transaction_log_bytes_reserved,
t.text,
q.query_plan
FROM sys.dm_tran_database_transactions trn
INNER JOIN sys.dm_tran_session_transactions se_tr ON trn.transaction_id = se_tr.transaction_id
INNER JOIN sys.dm_exec_sessions sec ON se_tr.session_id = sec.session_id
INNER JOIN sys.dm_exec_connections con ON con.session_id = sec.session_id
LEFT OUTER JOIN sys.dm_exec_requests req ON req.session_id = sec.session_id
CROSS APPLY sys.dm_exec_sql_text (con.most_recent_sql_handle) t
OUTER APPLY sys.dm_exec_query_plan (req.plan_handle) q
WHERE trn.database_id =DB_ID('TempDB')
This gave the option to identify the long running query and we killed the relevent session. With that, TempDB log file was emptied and by shrinking the tempdb log file, we were able to gain the disk space.
Further, we took a pro-active decision by enabling an alert, so that if a query runs for more than 8 hrs (configurable) that will be altered the DBA so that he can kill the session straightway.
In a previous blog post, it was said that new research was initiated in order to design and develop a framework for time series using agent technology.
In order to proceed with the research, it was decided to perform of feature analysis in various tools such as Microsoft SQL Server, Weka, Orange, Azure Machine Learning and Rapid Data Miner. Please comment if you have better tools.
The following figure shows the components for Microsoft SQL Server
Fast Fourier Series is used to detect the seasonality in SQL Server. Missing values will be identified only when there are multiple time series are presented. Mean, Constant, Previous and Same curvature are the techniques used to replace the missing values.
Further, Microsoft SQL Server has the capability of using the predicted values for further predictions.
References
https://www.sqlshack.com/microsoft-time-series-in-sql-server/
https://docs.microsoft.com/en-us/analysis-services/data-mining/microsoft-time-series-algorithm
https://docs.microsoft.com/en-us/analysis-services/data-mining/time-series-model-query-examples
https://docs.microsoft.com/en-us/sql/dmx/data-mining-extensions-dmx-function-reference
Further, every month Cheatsheet for the Time Series will be released. Please let me know your thoughts.
Transaction Log backups are important in a Production environment. It will make sure that you manage your log file size and keeping backups in case of a need to restore.
I am pretty much sure, most of you have scheduled transaction log backups. If you have scheduled Transaction log backups every 15 minutes, then you will see four log backups every hour and will result in nearly 100 backup files a day and you are looking at around 700 log backups per day. Unlike differential backups, you need all your lob backups to recover. Sometimes, you might have less or no transactions but still, there will be a log backup.
Now the question is, Can we create transaction log backup when there is sufficient size. Yes, you can if you are running SQL Server 2017 or later.
In sys.dm_db_log_stats Dynamic Management Function (DMF), there is a new column called log_since_last_log_backup_mb tells you what is the log file size after the last log backup.
Using the following script, you can perform transaction log backups when the log file size is more than a specific size.
DECLARE @log_since_last_log_backup_mb NUMERIC(9, 2) DECLARE @ThreasholdSize INT = 25 DECLARE @folderName VARCHAR(30) = 'D:\DBBACKUP' DECLARE @DatabaseName VARCHAR(30) = 'LB1' SELECT @log_since_last_log_backup_mb = log_since_last_log_backup_mb FROM sys.dm_db_log_stats(db_id(@DatabaseName)) IF @log_since_last_log_backup_mb > @ThreasholdSize BEGIN DECLARE @fileName NVARCHAR(400) = @folderName + '\' +
@DatabaseName + SUBSTRING(REPLACE(CONVERT(VARCHAR, GETDATE(), 111), '/', '')
+ REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), 0, 13) + '.bak' BACKUP LOG [LB1] TO DISK = @fileName WITH NOFORMAT ,NOINIT ,SKIP ,NOREWIND ,NOUNLOAD ,STATS = 10 END ELSE PRINT 'No BACKUP'
According to Gartner, 75% of databases will be in the Cloud by 2023. Since we are not very far away from 2023, as organizations we need to look at the possibilities of Cloud Databases. One of the greater challenges will be data migrations. As we know, organizations have a lot of data already. To Cloud Databases to become a success story, you need mechanisms and techniques to migrate your data to cloud databases.
Customers can start migrating with DMS at no additional charge for native like-to-like migrations to Cloud SQL. Support for PostgreSQL is currently available for limited customers in Preview, with SQL Server coming soon.
You can read more details at
https://datacenternews.asia/story/google-cloud-launches-new-data-migration-service
SQL Server Integration Service (SSIS) is a tool that is used to extract data from Heterogeneous data sources. There are a lot of options and features in SSIS that can be used for different scenarios.
These are some those articles.
Loading Historical Data into a SQL Server Data Warehouse
How to Retry SQL Server Integration Services (SSIS)Control Flow Tasks
SQL Server Integration Services SSIS CDC Tasks for Incremental Data Loading
Using the SSIS Script Component as a Data Source
If someone asks you "What is the best database", what is your answer. Answers might be different depending on your job role, whether you are a developer or a database administrator or client.
However, there is a ranking done for the database every month and the following is the latest ranking of databases.
Data Mining or Prediction has become a buzz word not only in academia but also in the industry as well. SQL Server is providing a rich set of algorithms to support data Mining for a long time. However, most of these features are not used due to many reasons. The following article series which I completed at sqlshack provides details of how to use data mining in SQL Server. The major important advantage is that you can use the existing data in the SQL Server with the Data Mining itself. Further, you have to option of using MS BI family for data mining.
Enjoy the article series here.
A client called and they had a slow system. Their complain was very simple.
They are a garment production company. Those garment items are flowing in a belt and there are workers who have a task of swiping the picked item to the bar code reader. Their complaint was that it takes more than 5 secs to read one production item. Their experience is that at the start of the season, this was around 1-2 seconds. When this is taking more than 5 seconds, they archived the data to solve the issue. However, they are looking at a permanent solution as this has been troubling them for a while.
Well, by the looks of it is very obvious that issue is an index. However, it was difficult to convince the customer, mainly client was not able to identify what is the index he should apply. Well, then it is decided to make the physical appearance by driving 40 kilometres.
At the client site, it took only ten minutes to find the troublesome query. SQL Profiler was initiated while asking the users to continue with the normal operations. The query was identified from the profiler and verified it by running it in the SQL Server Management Studio as in the query plan CX_PACKET was identified.
Then the index was applied to cover the where clause condition as it had only one column in the where clause. Well, 5 seconds was reduced to almost zero seconds making users very happy as they can earn more as an extra bonus.
The index is the most common problem in the database systems. However, the art of the index is identifying and creating them. It is something that needs a bit of experience.
If you need more details on Index read this article. Further, if you need more details on CXPACKET, this is the article.
If you are a database administrator, you will never know when your database hit a disaster. When the disaster hit your database, you will be in a panic mode and you will take action which you won't take in modest conditions. However, we need to plan for a disaster. This article provides you with the importance of Point in Time Recovery Method in order to recover data.
This is a fantastic feature in databases but you will not realize how valuable this until you come across this situation. Leaving describing these features to separate time, let us first look at two cases. Incidentally, these two incidents occurred in one organization and moreover for one database but at different times.
