31

Most of the information you will need is going to be in the execution plan ( and the plan XML). Take this query: SELECT COUNT(val) As ColA, COUNT(val2) As ColB, COUNT(val) + COUNT(val2) As ColC FROM dbo.TableA; The execution plan (opened with sentryone plan explorer) shows what steps it went through: With the stream aggregate aggregating ...


13

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table? In practice, yes (although not documented as such), but also useless. If your data never changes, then using dirty reads won't cause any observable behavior or material performance difference. If I have a ...


13

Microsoft says that DMV (ring buffers) doesn't work on SQL Server 2017, only 2019: Sys.dm_os_ring_buffers DMV has been a key DMV used for monitoring SQL Server by built-in tools as well as third party monitoring utilities. When SQL Server 2017 was released on Linux, unfortunately this DMV did not return correct CPU usage information by SQL Server ...


13

I'd ask the person who told you that, to at least hear why they think it will decrease performance. One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your ...


12

The log backup file shall contain all the transactions (including the ones that hasn't been committed yet) up to the moment the backup finished running and you can verify it as follows: CREATE DATABASE MyDB; ALTER DATABASE MyDB SET RECOVERY FULL; USE MyDB; CREATE TABLE LogTest (numbers int); Once created the database and the table that's gonna be used on ...


12

If you are talking about solving this purely in Transact-SQL, you can cover all those cases in your example data with the following filter: WHERE FilteredField LIKE '%[a-zA-Z][0-9]' If you want to additionally stipulate that all the characters before the numeral must be Latin letters, you will need to get a little creative: WHERE FilteredField LIKE ...


10

Could anyone confirm or denies that restoring the header only wouldn't affect the sys.fn_dblog or anything else? RESTORE HEADERONLY doesn't target any database in its syntax: RESTORE HEADERONLY FROM <backup_device> It reads the backup device and returns data to the client about what's in the backup. It won't affect your databases in any way.


10

No Ok, so that's the answer to your question, but you probably want to know why. Ok, so why not? Different databases might have different data, and thus need different plans. Say you restore the WideWorldImporters database to the same server TWICE (Let's call it WWI_1 and WWI_2). You've got two identical databases. SQL Server could possibly create one ...


10

That has made us wonder whether the backup process decrypts the data, compresses it, encrypts that result, then backs up that to disk. This is exactly how space saving is achieved. When backup process run page is decrypted and loaded into memory, then compressed and then encrypted and saved onto disk. The process is mentioned in Backup TSQL Doc Starting ...


8

multiple log files are bad It's not that multiple log files are bad... It's that multiple log files are totally unnecessary and provide no benefit ... unless you require a log file that is larger than 2TB. With data files, SQL Server can benefit from multiple files because it will perform parallel I/O to/from both files at the same time. However, with log ...


8

Test Data CREATE TABLE dbo.Table1( A INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,B varchar(255),C int,D int,E int); INSERT INTO dbo.Table1 WITH(TABLOCK) (B,C,D,E) SELECT TOP(1000000) 'Some Value ' + CAST((ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 400) as varchar(255))-- 400 different values ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) ,...


8

This would be a file created by using a DBCC. Summarizing the pertinent information in the link that Shekar Kola provided: The behavior of DBCC CHECKDB commands beginning with SQL Server 2014 DBCC CHECKDB creates an internal snapshot database. The internal snapshot database is created by using physical database files. Consider a ...


8

There is not a simple tool or existing script for this that I am aware of. In terms of scanning for the use of functions added in a specific version (or enabled by a specific compatibility level) something could be written using the documentation from MS though that might not catch calls via ad-hoc SQL if your stored procedure and other use that pattern for ...


7

There are many ways : If you can run the query you can use set statistics time on which will give you: SQL Server parse and compile time: CPU time = xx ms, elapsed time = xx ms. If you cant run the query, then mine the plan cache to check for query compile time along with many other significant insights. There are many other ways like profiler, ...


7

You don't need to drop the table(s). One option (that I have tested) is: Drop the partitioned non-clustered index(es); move partitioned clustered indexes, primary keys, and unique constraints to a non-partitioned file group; and unpartition heaps by creating a (temporary) clustered index on a non-partitioned file group. For help identifying which indexes ...


7

Data differences With a low amount of records to seek on, the optimizer is able to use the SIX_T_pathXQUERY index: and filter on moretext with a seek predicate: An interesting part here is that it executes the key lookup to get the path_1_id values that are not null. Since that is a filter definition on the nonclustered xml index... ...while not being ...


7

A possible solution is to create a server logon trigger that would check the app_name(). I created the following server trigger on my local instance and tried to connect via ODBC. The connection was rejected. You would have to modify for your needs. CREATE TRIGGER trgGetAppName ON ALL SERVER FOR LOGON AS IF APP_NAME() NOT LIKE ('Microsoft SQL Server ...


7

The feature designed to help in this scenario is Application Roles. With an Application Role you can allow the user to connect to the target database, but not be able to run any queries until the application supplies a secret to activate the Application Role.


6

In general you should be able to use most of the dbatools functionality from your laptop (or whatever host you use to connect to your server via SSMS). If you're the kind of admin who prefers to RDP onto your servers and use SSMS locally, then maybe try to break that bad habit; but in general you should be able to use dbatools alongside the rest of your ...


6

The question is, how can i make counting by xml as fast as counting by column? Depending on how flexible you need to be with your xPath expression you could use property promotion. create function dbo.GetSomeText(@X xml) returns varchar(8) with schemabinding as begin return @X.value('(/SomeText/text())[1]', 'varchar(8)'); end; go alter table dbo.T ...


6

That the 3rd party software requires the DLL does not seem relevant here. It seems that the issue is simply: I need ... to be able to write SQL Queries in SSMS and have that data sent to the third party application's API, which then would in turn do the correct data load/changes (inserts and deletes to/from this application has to be done via its API). I ...


6

What you see is correct. When you rebuild an index first the new index is built and only then the old index is dropped. And if you don't use SORT_IN_TEMPDB = ON the space that server needs to make a sort is allocated within your db Your db has now empty space, it's not space reserved to any object, it's just free space (space that was freed after temp ...


6

It almost seems like for each value returned, that value is assigned to the variable, then each row in the result set is subsequently assigned, almost like a loop. Yep. That's what happens. This is a side-effect of how variable assignment in SELECT queries were initially implemented. But it was used enough that it can't really be changed, and even is ...


6

One more point to add to the two good existing answers. Once you decide what the best size for tempdb is, you should manually set it to that size. If this is not done when SQL restarts monthly as the system is patched (assuming you do monthly patches) it will reset to the smaller size and will need to grow, giving the performance issues mentioned in the ...


6

You can just install SP2 and then CU10. No need to install CU for SP1. You can have a list of all SP + CU here More relevant info : If you have a large estate that needs to be patched, you can use dbatools - Update-DbaInstance Also, dbatools has a really nice - build reference page that will tell you what the latest SP/CU is and End of support dates as ...


6

The transaction log contains any transactions that were fully committed by the time the transaction log finishes. To use your example above, the transaction log contains A and B. This is similar to the full backup, which contains a tail-log backup of all the transactions that were committed when the backup completes. This site is excellent further ...


6

I don't really understand what you don't understand in the quote you included (strangely, as a screenshot), as it explains the difference quite clearly, in my opinion. Locks and latches have different scopes and lifecycles. Locks apply to what you might call database physical model elements -- tables, rows, index entries. Latches protect various memory ...


5

I suggest you create a custom database role by right-clicking the database role folder. That will bring up the Database Role that you can then customize. On the first screen you can give the role the name and owner of your choosing. You also can add the user you want to be a member of this role. Once that is done, you can click on Securables so you can ...


5

Using the follow link you can set everything up to track the exception This link In short: Set up a blank trace, add the following events : Exception under Errors and Warnings RPC: Completed under Stored Procedures RPC: Starting under Stored Procedures SQL: BatchCompleted under TSQL SQL: BatchStarting under TSQL From there on, you can see the error ...


5

Note the very last part of the error message you referenced: .. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized. If you look at the entire chunk of errors related to tempdb creation: 2019-11-01 22:37:07.20 spid9s Clearing tempdb database. 2019-11-01 22:37:07.23 spid9s Error: 5171, ...


Only top voted, non community-wiki answers of a minimum length are eligible