New answers tagged

2

One possible way to run a query very many times is to use a scalar user defined function as that will execute once per row in the query. If you have multiple CPU cores each one can support 1000ms / second which is why total CPU appear strange and go over what seems possible


3

One easy way is to ORDER BY the UNIQUEIDENTIFIER and if they are sequential you'll see a pattern. EG 866381AE-0976-EB11-A396-5C80B6738BD2 876381AE-0976-EB11-A396-5C80B6738BD2 886381AE-0976-EB11-A396-5C80B6738BD2 896381AE-0976-EB11-A396-5C80B6738BD2 8A6381AE-0976-EB11-A396-5C80B6738BD2 8B6381AE-0976-EB11-A396-5C80B6738BD2 8C6381AE-0976-EB11-A396-5C80B6738BD2 ...


2

To do this reliably you will need to upgrade to SQL Server 2019 and use the brand new DMV sys.dm_db_missing_index_group_stats_query There is a sample query with the documentation, I just became aware of this yesterday on Erik's blog. https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-groups-...


2

This isn't foolproof by any means but as a rough sanity check you can use the following query that leverages the DMVs to filter on the cached plan text's table name and index name that you plan to index: SELECT PlanText.[dbid] AS DatabaseId, DB_NAME(PlanText.[dbid]) AS DatabaseName, CachedPlans.plan_handle AS PlanHandle, OBJECT_NAME(...


1

I see from your comment that the errors are not happening so frequently as to be a performance concern. To answer the question asked, you could create a filtered XE trace to capture detail sql_batch_completed and rpc_completed events where result is Error. The events can then be summarized by a time interval for aggregated stats. The example below summarizes ...


0

I think your best approach would be to start with just tracking the counters in sys.dm_db_index_usage_stats. Just store the results off to a table (and have that run on a schedule) for as long as you feel necessary. Then look at the counters to see if there is significant movement on them. There will always be a little, especially if you have maintenance ...


0

Option 1 Not proceeding with [AlwaysOn Availability Groups] due to the increased complexity and licensing cost at this time I disagree with this characterization of AG (complexity is a shaky reason to not pursue what has become a fairly standard feature in MSSQL) and the claim of licensing costs being a deal breaker. The fact is, you're on 2014 which ended ...


-4

So achieving the best solution with the least amount of money happens to be my speciality. I would recommend using Redgate software's SQL Compare tool and run database syncs (Typically completes in less time than tlog shipping. I do these in 3 minute intervals, I have my DB read server separated from my DB write server. Focus reporting and DB retrieval from ...


1

The dtsx package runs where you install and execute it. Usually in SSISDB. Then you can create a sql server agent job to run it. The connection string specifies where the action (executing some sql script, or as a data source/destination in a dataflow as examples) will be executed. You can install the package in every server you want and then refer in the ...


2

In a database where SQL Server Audit was not previously configured you could try to Read SQL Server Transaction Log Backups to Find When Transactions Occurred with the use of the undocumented function fn_dump_dblog. For future situations where you need to audit a database I advise you to Create a server audit and database audit specification. Unfortunately, ...


4

you can use GRANT ... WITH GRANT OPTION. https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver15 this option specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts.


0

Error 18456; Severity 14; State 38 Failed to open the database specified in the login properties. (or) Cannot open database "" requested by the login. The login failed. State 38: Could not find database requested by user. Run the following command and check the result: SELECT * from sys.databases Depending on the results, we can identify how we ...


1

I have spent a decent amount of time searching for an explanation of the different naming conventions on some of my own databases, and haven't found anything definitive. That being said, in both our cases we are dealing with very old databases that have undergone many upgrades over the years. My best guess is that at some point Microsoft switched the auto-...


0

HeidiSQL or DBeaver manage the SSH connection and not ODBC. You'll need to create your SSH connection with a port forward first. Then test if you can connect a mysql client to the local ssh forwarded port. Then attempt to create your linked server using ODBC to local end of the ssh forwarded port.


1

What exactly do you mean by "remote"? Meaning on the network, just not hosted on their computer? In general, you don't need to assign any elevated permissions to any person or application user. You only need to create a stored procedure that does the very specific steps required for this operation, grant the required permission to the stored ...


0

If you use the non-deprecated equivalent sys.database_files view's column physical_name, does it provide you the full path?


5

SQL Server 2016 improved the perf somewhat, but the basic reason is the STDistance is accurate, but simpler formulas can be faster, if you understand the errors they produce. See eg Geographic distance can be simple and fast, and a description of the algorithm used in STDistance: This paper outlines some geometric algorithms that are used in Microsoft SQL ...


2

You can ignore the implicit conversion warnings, they are irrelevant here. But parsing XML is not free, and you're doing it the best way possible in TSQL. If you really need to improve the XML parsing, you'd have to try SQLCLR and the low-level XmlReader, or a pre-compiled XmlSerializer.


2

Yes you would need a Login for that domain user to exist first. If all they need is the ability to manage databases (create, alter, drop, restore them) then the server role dbcreator should be sufficient. If you anticipate multiple domain users needing access to this, then you should instead create a Login for a correlating Active Directory Group, so you don'...


3

The quick answer is yes, but only for the user permission on the DB, if you got login and user permission on the server-side, you need to copy these ones to the secondary server to avoid the possible issue in case you do a fail over to the secondary server


5

Most likely your problem is caused by the databases going through recovery to redo or undo transactions that haven't been hardened to data files. Avoid recovery all together Before doing planned server reboots or failovers of an FCI, particularly one with a lot of memory, I like to run a CHECKPOINT on every database. This minimizes the time spend on a clean ...


Top 50 recent answers are included