New answers tagged sql-server
3
votes
In-Memory Table Type with Primary Key causing Eager Index Spool
nope
The premise that in-memory should have better performance for select queries is absolutely false. The ideal use-case for it is "hot" data that is under frequent, highly-concurrent ...
1
vote
Size of MDF file is not getting updated after adding transactional log file(s) in SQL 2022
OP is you, the "original poster" (this was asked in the comment section).
It seems that you expect the ldf file to increase and/or decrease in size as you restore log backups. Is that the ...
- 17.8k
2
votes
Frequent Statistics updates in SQL Server 2022 Enterprise Edition
Check your compatibility level. Statistics maintenance and the update frequency were changed in SQL Server 2016 and behaves differently. You can read about it in the documentation here. Depending on ...
- 4,439
1
vote
Left join 2 tables and return many duplicate rows
You haven't answered the question on why type of 1 matches with a and 2 goes to b etc. But based on your example it seems like you just want to match them up lexicographically. Assuming that's true, ...
- 38.6k
0
votes
How do I remove non-space trailing character(s) from a string?
Since SergeyA hasnt made it an answer, this is another way to do it.
select case when item like '%/'
then REPLACE ( item , '/' , '' )
else item
end
- 1,765
2
votes
How do I remove non-space trailing character(s) from a string?
If you are on SQL Server 2022+, then you can use RTRIM, as a 2nd parameter to the function (along with LTRIM) was added to denote the character(s) to be trimmed:
SELECT RTRIM(item,'/') AS item
FROM ...
- 285
0
votes
Accepted
Left join 2 tables and return many duplicate rows
Your key emp has multiple matching rows for each emp in table2. The engine has no way to know that
1 maps onto a
and 2 maps onto b.
What it is returning is correct. To get the answer you want, you ...
- 1,765
1
vote
Accepted
SSRS on a Secondary Replica
So you have a secondary replica that is not entirely healthy, and you're asking if you should install a web application on that server as well?
No. The answer is no. If everything was running great, ...
- 47.4k
2
votes
Does the performance impact even happen on simple select from view with where clause of it isn't used in JOINS?
For
select * from linkedserver.db.tbl.viewname where col1='test'
How do you know which server will evaluate the col1='test' predicate?
There's an answer, but the fact that it's not obvious means that ...
- 47.4k
2
votes
In the dm_tran_locks DMV in SQL Server, why is the request mode set to "S" on an update statement
Those S locks are on the database. They prevent other users from dropping the database or changing its state.
- 47.4k
0
votes
Does the performance impact even happen on simple select from view with where clause of it isn't used in JOINS?
I'm unable to understand why statistics will matter if the query is as simple as ... select * from linkedserver.db.tbl.viewname where col1='test'
Why would this query be inefficient if it were run ...
- 9,230
3
votes
Accepted
Query has been cancelled because the estimated cost of this query exceeds the configured threshold
This doesn't involve the resource governor, user options, or long-running queries; it's a system configuration based on estimated costs (though there is a SET command to achieve this for the current ...
- 181k
0
votes
Guidance on how to assess the impact of multiple parallel SELECT queries
How can I determine if the workload of their queries is causing issues?
Use a trace or performance analysis tools like sp_WhoIsActive.
What signs should I look for?
Long running queries (longer ...
- 38.6k
7
votes
Can a dropped table be recovered from Always On availability group?
Sean already covered the answer to your specific question.
I think it's worth pointing out that, since you're using Availability Groups, you are required to have the database in the FULL recovery ...
- 29.7k
3
votes
Hash join hint returns an error when using local variable
Your query:
DECLARE @Id INT = 18471
SELECT c.UserId FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
ON c.UserId = u.Id
AND c.UserId = @Id
has c.UserId = u.Id and AND c.UserId = @Id.
By ...
- 88.3k
4
votes
Accepted
Can a dropped table be recovered from Always On availability group?
If you do anything in the database context on the primary then those changes will be reflected on the secondary replicas. In your case, if you dropped the table, assuming the secondary replicas are ...
- 34.9k
0
votes
Can't create a table with the same name I deleted before
drop and create table using schema sql helps, first generate CREATE table script, then drop table by sql, and modify CREATE script, then execute CREATE script.
If you using ssms, restart ssms works.
- 101
3
votes
SQL Server: how Row Goal affects the same index operation?
Summary
The plan with the row goal encourages the storage engine to use row level locking at runtime, while the plan without the row goal uses page level locking. Taking and releasing a large number ...
- 88.3k
0
votes
Log table without primary key
The additional cost of INSERT here is trivial, unless you have high enough insert volumes to hit hot page latch contention, which only starts to kick in around ~10,000 rows/sec, and since it's just ...
- 47.4k
-1
votes
Log table without primary key
The main benefits I can think of of using a table as a heap in SQL Server, for example in your case a table with an identity column and no primary key and assume another indexes:
Faster Insert ...
2
votes
What could have resulted in successful restore and dbcc check, but with missing data file (ndf)?
If your compressed full backup was not performed WITH INIT, then probably:
it was appended to an existing backup file that already had other full backups in it
one or more of those backups - at ...
- 181k
0
votes
Login failed for user 'DESKTOP-KRBN07G\juan_'
Well, guys, I found the problem, it was a wrong and 'hidden' Connection String.
I created a new database with a different name, but the error in the browser after launching the app in my localhost ...
0
votes
Login failed for user 'DESKTOP-KRBN07G\juan_'
It might be that the SQL Server is set not to allow access except by Windows Authentication, I've seen this allow connections from DB tooling but not Apps etc.
If you check your logs it will be clear ...
- 111
5
votes
Should tiny dimension tables be considered for row or page compression on servers with ample CPU room?
From a performance perspective, no, you should focus on tables where compression savings help you make better use of the hardware your SQL Server has assigned to it.
But from a standards and practices ...
0
votes
Query is fast in the proper DB, slow in master
In this case the Legacy Cardinality Estimation (found under database > Properties > Options) was set to ON for the CRM database but OFF for the master database.
The options to get consistent ...
- 181
0
votes
Log table without primary key
I would have thought if its an identity column it would have to be
checking the data anyway to do an insert at correct numbers, should
this be set up as a pk for that reason?
No, that is not how ...
- 17.8k
4
votes
Accepted
Do I need a `HOLDLOCK` on a `MERGE` that's not sourcing from another table?
I need to know whether the following pattern requires me to use HOLDLOCK in a very highly concurrent environment.
The HOLDLOCK hint applies SERIALIZABLE isolation semantics to the hinted table. This ...
- 88.3k
0
votes
Log table without primary key
First of all, PK is not Clustered Index, although in SSMS it seems so.
In short, clustered index should be built.
To insert rows in the heap also need read IO, which must search IAM(Index Allocation ...
- 131
0
votes
Log table without primary key
Give it a PK. This helps in multiple aspects of data management, not just logical.
For example, suppose at some point later you decided to ship this data somewhere and whatever gatekeeper requires and ...
- 567
12
votes
Accepted
MSSQL - query runs for too long when filtering after a certain date
rows and goals
The problem you're hitting when data isn't there is somewhat(?) common. I've blogged about it in a post helpfully titled:
SQL Server Query Performance When You Search For Rare Data ...
2
votes
Accepted
Given dm_db_missing_index_details output, what index(es) would support the following recommendations
Well it would be somewhat lengthy of an index, but including all of the columns in your include list (that aren't already in the main index list) would make the index fully covering for all of those ...
- 38.6k
1
vote
Nothing happened in my table according to "fn_dblog", is that correct?
2little2late
The function you're using (fn_dblog) will only show active log records in your transaction log. That means if you're in full or bulk logged, and someone has taken a transaction log backup,...
2
votes
Accepted
How can I do arithmetic in the ORDER BY clause with a column aliased in my SELECT clause?
You can use a number of CROSS APPLY which feed into each other to add "virtual" columns.
SELECT
v1.evil_column
FROM
sys.tables t
CROSS APPLY (
SELECT t.schema_id + 2 AS evil_column
) ...
- 13.9k
1
vote
Creating a global alias for a SQL Server Instance
Another option is to make a single DNS alias, of say three letters, then get each instance to listen on different ports. Then your connection string can use
Sql,1433
Sql,1437
Sql,1438
- 13.9k
4
votes
Accepted
Sessions killed when AG group connection terminated
What doesn't make sense is that when the AG group failed, all active SQL agent jobs running on the primary had their sessions killed. Why did this happen?
If a primary replica has a role change (it ...
- 34.9k
0
votes
Azure SQL Error - 'Resource ID : 2. The session limit for the database is xxx has been reached.'
This issue was fixed with the latest Office update (version 2408 - 17928.20156).
Thanks for the comments that were posted.
- 101
-1
votes
Accepted
Unstable plans when two first index columns have NULL values in all rows
I hate to approve my own answers, but this time I think I got it right.
After 3 days of tinkering with this issue I came to the conclusion that SQL Server gets lost with single-step statistics. After ...
- 204
-2
votes
What is best way to change primary key of large table with minimal downtime?
disable the primary key and drop it. and create a new one.
- 1
9
votes
Accepted
Creating a global alias for a SQL Server Instance
There is no such thing as a global alias, aliases are artifacts of the drivers and as you've found out are local to the profile.
The proper way of handling this (other than not tripling up instances) ...
- 34.9k
3
votes
Is there any benefit to declaring an index that contains a primary key as unique?
There is another benefit in declaring the index as unique.
Every non unique index is always bigger than the similar unique index.
This is because in case of non unique nonclustered index, the rowID(...
- 11.1k
3
votes
Unstable plans when two first index columns have NULL values in all rows
You can create a partial index with only the NOT NULL values:
create index ix_test on t1 (c2, c3, c4, ID) where c2 is not null and c3 is not null;
This index will be extremely small (and fast) and ...
- 1,653
0
votes
Accepted
How to get partition scheme for filestream data?
Found a way outside of SSMS using PowerShell. I used dbatools, but it will probably also work with the SqlServer module.
$Tables = Get-DbaDbTable -SqlInstance localhost -Database FSPartitionTest
$...
- 93
10
votes
Accepted
Is there any benefit to declaring an index that contains a primary key as unique?
Uniqueness and Performance
Arguments FOR Declaring Uniqueness
It's typically better to provide more information to the optimizer rather than less. If you know that an index is unique, it's best to ...
- 29.7k
1
vote
SQL Server 2016 : upgrade from SP2-GDR to CU15
I was experiencing troubles installing SQL Server 2017 CU on previously GDR system. In September 2024 the last CU 31 for 2017 had already come out, and in addition a new security update for GDR was ...
- 11
1
vote
Accepted
Track the connection history to a certain table in SQL Server
If you're running Enterprise Edition, you can use SQL Server Audit to audit the SELECT statements against a specific table.
If you don't have Enterprise Edition, you can look at using Extended Events ...
- 10.1k
0
votes
SSPI handshake failed with error code 0x8009030c, state 14
I had a developer who couldn’t connect to our development database but they were able to connect to testing and production (read-only of course!). We use AD authentication for all of our environments. ...
- 196
8
votes
Unstable plans when two first index columns have NULL values in all rows
-- When you check the stats histogram, there's none.
That's a bug in sys.dm_db_stats_histogram. DBCC SHOW_STATISTICS shows the NULL row correctly (this is what SSMS uses). The original problem with ...
- 88.3k
0
votes
Accepted
Added Articles Are Being Ignored
Answering this myself. I've found many variations related to this. In my case it boils down to the fact that I was not running a trace while adding articles in the UI. I found a post that states that ...
- 567
7
votes
Accepted
Does plan guide consider spaces or not?
different worlds
SQL Server's documentation has always been a bit obtuse, but not necessarily incorrect. What you're looking at is information for two different things, plan guides for SQL statements, ...
3
votes
Query is fast in the proper DB, slow in master
The context database determines important optimizer behaviours, depending on the setting of compatibility level and database scoped options like which cardinality estimation model is used.
It doesn't ...
- 88.3k
Top 50 recent answers are included