New answers tagged

0

At my last job this is exactly what we did for a very similar scenario (also on SQL Server 2008 R2 - Standard Edition), and I was the one to build it out. We had an off the shelf vendor application we heavily used and needed to report out of. It was indexed differently than how we were reporting out of it. We had one way transactional replication setup to ...


1

You need to find all of the distinct groups of parameter values for a given proc, and then sort by the count of those groups. Something like this: SELECT spc.Param1, spc.Param2, ..., spc.Param99, COUNT_BIG(*) FROM dbo.StoredProcedureCall spc WHERE spc.StoredProcedureName = 'sp_YourProc' GROUP BY spc.Param1, spc.Param2, ....


5

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups? The easiest way to have a block of code that can be run by only one session at a time is to use an Application Lock. You can use SQL Server's locking engine, but instead of locking a specific row, page, or table, you create a lock with ...


0

As mustaccio pointed out you must set a partition in the ROW_NUMBER() function, just to start the counter again for each LocationId. I've set up the next example: create table locations (id int identity primary key, location_id varchar(10)); insert into locations values ('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'), ('001'...


1

As mentioned in the comments, an index on StartDate will let your query seek right to the row it needs, rather than scanning the whole table or another index (which is what it's likely doing right now): CREATE NONCLUSTERED INDEX IX_StartDate ON dbo.ProductActivity (StartDate DESC); Like any other index, this one will need to be maintained anytime a row is ...


1

Thank you @Dan Guzman. rpc_completed was the missing event for me. Unfortunately there's no simple filtering possible an a query hash because there isn't one associated with the sp_executesql procedure call. Thus I chose to filter on the sqltext itself (possibly a costly operation but it works). Here's my final XEvent session in case somebody can take ...


2

You might want to query relevant tables like: sys.dm_exec_connections sys.dm_exec_sessions sys.dm_os_waiting_tasks sys.dm_exec_requests sys.server_principals sys.databases sys.dm_exec_sql_text() sys.dm_exec_query_plan ...to have an overview of what is happening even if nothing is happening. Query Running Tasks (and Other Stuff) Run ...


1

Do you have single spinning disks as storage? If so, you might see possible advantages from defrag. If you have more modern storage, then is is likely that you defrag but only see marginal benefits from the hassle. I've written three blog posts on this, I suggest you start with the last one: http://sqlblog.karaszi.com/fragmentation-the-final-installment/ If ...


1

and we have a scheduled REINDEX maintenance job that runs every early morning at 4 am. Wait..is that really necessary to run the reindex job every day ?. Do you even look at whether the indexes are fragmented ?. Since you said its a critical system I will take liberty of assuming the underlying storage is fast that further lowers the possibility of re-...


1

So, a covering index is an index that has all the columns that are needed for your query. A key lookup happens when the optimizer uses your index to get data, but is lacking one or more columns, so it fetches those from the clustered index. As for example you have an index seek on Table_22 with the index table_22_promoId_Index. Afterwards in the plan you ...


1

Your merge statement is not correctly formed and will always insert all the records, so you will need to update it and tell it to insert the records not matched in the destination table using WHEN NOT MATCHED like this: MERGE table USING (SELECT ? AS Search_Col) AS SRC ON table.blobTask_id = SRC.Search_Col WHEN MATCHED THEN UPDATE SET x= ?,...


2

This appears to be a conflict between the instance (i.e. server) -level collation and the collation being used in the [master] database. Both sys.server_event_sessions and sys.dm_xe_sessions are system catalog views, and the collation of their [name] fields should be the same. sepupic had the O.P. execute the following: select top 1 sql_variant_property(...


1

For the sake of completeness, you can SET ANSI_NULLS OFF, which is a session setting. I just mention this since it still exists, and also to be very clear that this setting is deprecated. I.e., according to MS documentation, this setting will (can) be removed in the future. See https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-...


5

This isn't the most elegant solution, but it'll work to return matching rows if @YEARCOMPLETED is a value, or if it is null. This works decently because it's able to make use of an index on the yearCompleted column, but it basically doubles your code with a near duplicate: DECLARE @YEARCOMPLETED smallint; SET @YEARCOMPLETED = NULL; SELECT * FROM TABLE1 ...


1

Thanks for the help. I found out it was a HIPS (Host Intrusion Protection System) blocking the registry entries. Once I disabled it, the installation completed successfully. Thanks again.


1

You can use OR: WHERE yearCompleted = @YEARCOMPLETED OR yearCompleted IS NULL


2

My problem with this DMV is that it misses the critical context of time, so it's difficult to pinpoint when the spikes in latency occur. If you're running a VM rather than an RDS instance, I'd recommend setting up a Perfmon Trace to collect disk latency metrics so you can narrow down the times. If that's not an option, push the results of your query to a ...


0

The SQL Server error log shows problems including performance counter initialization issues and SSL problems. Have you recently changed anything about the configuration of the machine or SQL Server itself? At this point, my first reaction would be to re-install SQL Server, after ensuring you have reliably backups of any business critical database files.


1

As far as the SQL Server product is concerned, the SHRINKFILE operation is safe. It is designed to respect locks, recovery models, transaction log usage, etc. In the absence of a bug in the SQL Server code, shrinking a database file won't cause corruption of data loss. All code has bugs, so there is a chance that a routine operation like shrinking a data ...


0

This morning this job failed again, also I find the same time another Symentic Antivirus Job was running: Scan type: Tamper Protection Scan Event: Tamper Protection Detection Security risk detected: C:\PROGRAM FILES (X86)\EMC\UNISPHERE HOST AGENT\HOSTAGENT.EXE File: C:\Program Files (x86)\Symantec\Symantec Endpoint Protection\14.0.2415.0200.105\Bin\ccSvcHst....


1

If you're making regular backups of the log file or are running in SIMPLE recovery mode, the log file should mostly consist of free space; shrinking it will just truncate the free space from the log file. This is usually safe to do, but you need to realize that the log file will likely just grow back to the previous size....when this happens the log will ...


0

Whatever account you're using to install SQL doesn't have permissions to modify the registry and the file system. You should run the installer using an account that has local administrative access.


4

It's quite common in Data Warehouse schema designs to assign a new, integer, single-column key to every table, and retain the source-system key (sometimes called the "business key") as well. Kimball says: Natural keys created by operational source systems are subject to business rules outside the control of the DW/BI system. For instance, an ...


3

Here's a repro. It's documented that you can't use FORCESEEK on an indexed view without NOEXPAND. EG drop table if exists table1 drop table if exists table2 go create table Table1(IndexedField1 int, OtherField1 nvarchar(200), OtherField2 nvarchar(200)) create table Table2(IndexedField1 int, OtherField1 nvarchar(200), OtherField2 nvarchar(200)) go create ...


1

If I understand the question, yes this is a supported scenario and it can be done quite easily without as much work as the other answers. You do not have to remove the remote node from the AG or create a new AG to do this. High level steps would be to go through the SSMS wizard to add the DB to the AG, and at the end hit the button to create the scripts, ...


2

Recreating the tables CREATE TABLE dbo.Messages(MessageID BIGINT NOT NULL PRIMARY KEY, MessageThreadID bigint not null, Deleted bit null, FromUserID bigint null, ToUserId bigint null, Message nvarchar(max) not null, SendDate Datetime not null, ReadDate datetime null); CREATE TABLE dbo.MessageThreads ( MessageThreadID bigint not null PRIMARY KEY, ...


0

Most of the time, it runs fine, however, sometimes, when accessing records from the DB, the application is awfully slow. Interest - and accurate - turn of phrase, there. The Application is suffering poor performance. It is unknown, at this point, whether the Database is (or not). Generally speaking, Databases don't run slowly - Queries do. You need ...


3

The value seems to get updated when a change is attempted on the file itself, such as LowlyDBA suggested. But only when the disk has been set to read only when no more modifications have to happen on the secondary data file. If there are still changes to be made, a different error will be shown. For example when creating a database that has one data file on ...


1

Column 'CUSTOMERS.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. This is because you're selecting more columns from your query than you're using in your group by. All columns in your SELECT clause have to either be in your group by, or be an aggregate. So after knowing that, it ...


0

Is this a supported scenario? If so...What would be the recommended approach? Yes, you can do that, one of the way by creating new availability group so that exiting AG databases would not be interrupted, an availability group can be created without adding database and additional replicas into it. In your case you can follow the same approach, following ...


1

GROUP BY age will "compact" a group of records with the same age value into one record. But each record in a group have its own id (and another fields) value - what value does you want to obtain in this single "compacted" record? I think that you need SELECT customers.* FROM customers JOIN ( SELECT age FROM customers GROUP BY age ...


0

I would recommend you either restore/attach the database to the primary node on the SQL server. From there you could add it to the AG but not have it replicate. Ultimately, to get it on the 3rd(remote) node - you'd likely have to break the AG and re-add the database (if you're allowed the downtime) after it's already copied to the remote node. This would ...


2

An indexed view is expanded during query compilation unless the NOEXPAND table hint is used. The warning is generated on that expansion. In Enterprise Edition, the optimizer may later match the expansion back to the materialized view. This is a cost-based decision. In anything other than Enterprise Edition (or equivalent) you must use a NOEXPAND hint to use ...


0

In Azure you cannot restore on an existing database. (c) Apparently, not really. You CAN restore a .bacpac file into existing Azure SQL database, for example, in a case when you really need to keep the same instance of Azure SQL database, as it's incorporated in Azure environment. A necessary condition for that is: the target DB must be empty, i.e. like ...


0

Most of the information you will need will be found in this post by Erik Darling. ...SQL Server is sort of smart about some modifications. Why it isn’t smart about all of them is beyond me Inserts are fine when we adapt and run your queries: CREATE TABLE dbo.BoringTable(ID INT IDENTITY(1,1),DateColumn1 DATE,TextColumn1 varchar(50),TextColumn2 varchar ...


1

If you have the max degree of parallelism set to zero, which is the default, then queries that might benefit from parallelism will consume 32 threads. With the number of workers set to 960, again the default for your setup, you could only run 30 simultaneous parallel queries. I'd recommend setting max degree of parallelism to something sane, like 8.


26

CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019 2:11PM" and output dependeds on language). To make it deterministic use CONVERT and the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic,...


6

While a good DBA can find out a lot on his own, it never hurts to give a lot of information. Which application you're using. If you know server/db ect give that as well. Test/Prod When the issue occurs, eg. I open this form and it's slow, or when I do this... What's slow and what's fast. For some applications it can be perfectly acceptable to wait 1-2 ...


1

The simplest thing to ask is for the DBA to enable the Query Store, and grant you the VIEW DATABASE STATE permission. This will track query performance and give you the ability to identify slow-running queries with SSMS.


-3

First thing you can do is figure out on your end what specifically causes this to kick off the slowness. Is the number of clients Is running a reports from multiple clients Is it a network issue if the SQL server is in the cloud.... etc.... Armed with that information you can then tell the remote DBA monitor the database and we will cause the slowness ...


0

From the best practices guide: VMware recommends setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform. That means that a VM that requires 8 CPUs will have 8 vCPUs with 1 core each. That seems like bad advice (doesn't seem to be in the current guide) as as setting 1 core for 8 CPU's would mean that ...


0

SELECT id FROM table GROUP BY id HAVING COUNT(DISTINCT gender) != 1 OR COUNT(DISTINCT FirstName) != 1


1

#temp is session scope CREATE TABLE (Transact-SQL) - Temporary Tables A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. Do the official microsoft way no tricks, it would end in a mess


7

I would add OPTION(USE HINT 'DISABLE_OPTIMIZER_ROWGOAL') to the query to disable row goals. The easiest way to spot the use of row goals is when using the TOP() operator, used to return 45 rows as quickly as possible in your case. The use of loop joins is more common here as it is estimating smaller result sets & using non blocking operators. Row ...


0

The typical solution in OLTP is to copy the relevant attributes onto the Order or Order Details table. Eg handle it like the product price, which is always copied onto the Order. So the name of a Product at the time of the Order is modeled as an attribute of the Order, not through the relationship with the Product table. In Data Warehousing, the typical ...


1

This is what I ended up going with. --==================================================================================================================================================== -- Get tables in Snapshot replication for the selected DB. --===============================================================================================================...


1

It's actually a very good question and can go a lot deeper but I'll give you the short version. It all boils down to your scenario. If your requirements demand this dataset to be always sorted in the same way, consider creating an index to tune the query. If your requirements don't demand any kind of sorting, don't bother yourself (and the engine). If ...


1

For a start: You are missing a number of parentheses (in any case). Then: You need to combine different records into a single result. One way has been shown by @Akina. Alternatively, you can run against two copies of the same table: WITH Part1 AS ( SELECT userID , question , answer FROM Survey_2019 WHERE question IN ('I am trying to ...


1

I have this survey table and I'm trying to gather the information of userid who answer Strongly agree to the question of I'm trying to watch my weight OR I love drinking Coffee Black AND must have answer Agree OR Neutral OR Strongly Disagree to these questions I thrive when a deadline is close and I'm all about trying something new. Simplified: [...


1

One way to do this: with stats as ( select agentname,CallCenterID, count(leadid) Dials from LeadCallHistory where agentname not in ('vdad','system') and cast(callCenterStartTime as date) between cast(getdate()-1 as date) and cast(getdate()-1 as date) group by agentname, CallCenterID ) select * from stats s1 where exists ( select 1 from ...


Top 50 recent answers are included