Are the posted questions getting worse?
-
GilaMonster (4/8/2016)
Jack Corbett (4/7/2016)
SQLRNNR (4/6/2016)
Brandie Tarvin (4/6/2016)
Folks, we have someone who is deliberately trying to force a deadlock to test some stuff. Can anyone give him advice on how he might do that?Dang it - too late to that party.
I have 3 different routines that guarantee a deadlock. Grant gave him something works great. Plus he was able to repro his deadlock in test - which is great news.
All you need is Navision and you can find plenty of code that generates deadlocks.:-D
If you want some deadlocks, the system I'm currently fixing generates about 200/hour. On a 30GB DB. With ~25 concurrent users.
oh la la.
That really sounds like a lot of fun.
I had a client that wasn't quite that bad but they generated upwards of 1200 deadlocks a day.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
You can win a conference pass to SQL Bits. Post a review of a previous event, or of one of the videos from a previous event (available online) : http://sqlbits.us4.list-manage.com/track/click?u=3e64686fd47743f72cf7ae22a&id=8d1ba01f21&e=3d669e3feb
-
BL0B_EATER (4/8/2016)
Would anyone find an article on Azure's SQL elastic pools worth reading? More on the basic setup and screen navigation? Just to understand where you would add a DB, change DTUs etc?thoughts?
Yes & Yes. all of it. Write a couple of artices, especially if you have this stuff in production.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant Fritchey (4/8/2016)
BL0B_EATER (4/8/2016)
Would anyone find an article on Azure's SQL elastic pools worth reading? More on the basic setup and screen navigation? Just to understand where you would add a DB, change DTUs etc?thoughts?
Yes & Yes. all of it. Write a couple of artices, especially if you have this stuff in production.
Definitely, even though I only have a small clue what it is, which is why I need the article.:-D
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack Corbett (4/8/2016)
Grant Fritchey (4/8/2016)
BL0B_EATER (4/8/2016)
Would anyone find an article on Azure's SQL elastic pools worth reading? More on the basic setup and screen navigation? Just to understand where you would add a DB, change DTUs etc?thoughts?
Yes & Yes. all of it. Write a couple of artices, especially if you have this stuff in production.
Definitely, even though I only have a small clue what it is, which is why I need the article.:-D
Ok excellent I am working on this and a geo replication article.
-
Grant Fritchey (4/8/2016)
BL0B_EATER (4/8/2016)
Would anyone find an article on Azure's SQL elastic pools worth reading? More on the basic setup and screen navigation? Just to understand where you would add a DB, change DTUs etc?thoughts?
Yes & Yes. all of it. Write a couple of artices, especially if you have this stuff in production.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
That's ridiculous. A person claims to "brain dead" and (actually said bread dead) can't read an article so asks for an example when the article itself has an example. Migraine or not, someone thinks their entitled.
-
I'm researching for an article and a blog post on Stretch DB, and I'm struggling to find anything complimentary to say about it to be honest.
How many here have a transaction-type table where you might be interested in moving the older rows to Azure where:
The table is not referenced by foreign keys
The table has no check constraints
The table has no default values
The table has no computed columns
The DB and all string columns in the table are SQL_Latin1_General_* collation
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (4/8/2016)
I'm researching for an article and a blog post on Stretch DB, and I'm struggling to find anything complimentary to say about it to be honest.How many here have a transaction-type table where you might be interested in moving the older rows to Azure where:
The table is not referenced by foreign keys
The table has no check constraints
The table has no default values
The table has no computed columns
The DB and all string columns in the table are SQL_Latin1_General_* collation
Except for the "no default values" thing (for DT of entry) and the "any color as long as it's black" collation thing, it sounds "perfect" for "audit-only" and other "WORM" tables such as closed invoice/invoice detail tables. In fact, MS advertises Stretch DB as just exactly that...
Stretch Database migrates your historical data transparently and securely to the Microsoft Azure cloud.
(source: https://msdn.microsoft.com/en-us/library/dn935011.aspx)
Whup! There's a huge fly in the ointment, at least for my audit tables, which are necessarily EAVs and that's the fact that it also does NOT allow SQL_Variant as a data type. For those that store XML as single unit blobs, you'll need to use a different data type, as well. Even if those seem like minor annoyances to most, this next one might be a killer for some although one does have to remember that they were designed for historical tables...
•Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints on a Stretch-enabled table.
(Source: https://msdn.microsoft.com/en-us/library/mt605114.aspx)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (4/8/2016)
it sounds "perfect" for "audit-only" and other "WORM" tables such as closed invoice/invoice detail tables. In fact, MS advertises Stretch DB as just exactly that...It's the foreign key references I mainly have a problem with.
OK if you keep historical data in another (constraint-free) table, but if you keep the data in one table and want to move the older stuff, there's likely to be at least a foreign key between order and order detail
this next one might be a killer for some although one does have to remember that they were designed for historical tables...
•Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints on a Stretch-enabled table.
(Source: https://msdn.microsoft.com/en-us/library/mt605114.aspx)
I'll have to check, but the warnings I was getting said that the unique isn't enforced on the portion of the table that's in Azure. That may be OK, as that's read-only data. The portions of the table that have been moved, cannot iirc, be modified.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
For me, the restriction to SQL collations would have been a killer with several of the databases I've had to look after even if I'd been prepared to accept non-enforcement of uniqueness, which would require changes to operational procedures or the addition of triggers to enforce it, since it would be possible, for example, to insert the Q1 2015 data from the production system into the history system twice because the primary keys' uniqueness would not be enforced. Changing from Windows collations to SQL collations would have been a non-trivial task, since many queries contained explicit collation directives for comparisons in join or where clauses. I don't for a moment believe that the devlopment effort to do the changes would require any brain-power, but there would be a lot of things that needed to be done to ensure it all came together properly, and QA and system test would have been very expensive.
Tom
-
TomThomson (4/9/2016)
For me, the restriction to SQL collations would have been a killerThe collation limitation's not documented, so I'm wondering if it's a bug (though whether a bug in the product or the documentation is another matter).
All the other restrictions stop you from selecting a table to be stretched (from the wizard), collation throws an error half way through the setup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Most of the tables I have fitting that description are Staging tables which import data from other sources so I can massage it and stick it in my data warehouse. I believe that our reporting team has some datamarts (don't get me started on the flatness here) fitting this description. There are indexes, though.
EDIT: removing extra words.
-
I need some help in this topic.
Short story: OP tries to give users access to a view but not to a base table. First went about this wrong (different owners, no ownership chaining). Based on my suggestions they change the objects to all have the same owner and now the security works as expected.
With one exception. In a script that has a USE statement, a GO, and then a SELECT from the view, they get a permission violation. Remove the GO between USE and SELECT, or remove the USE completely, and the violation goes away.
I am at a loss to explain this. If anyone has an idea, please chime in!
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (4/11/2016)
I need some help in this topic.Short story: OP tries to give users access to a view but not to a base table. First went about this wrong (different owners, no ownership chaining). Based on my suggestions they change the objects to all have the same owner and now the security works as expected.
With one exception. In a script that has a USE statement, a GO, and then a SELECT from the view, they get a permission violation. Remove the GO between USE and SELECT, or remove the USE completely, and the violation goes away.
I am at a loss to explain this. If anyone has an idea, please chime in!
You got hold of good one there, Hugo. I gave it a shot, but I really doubt that'll be it.
Viewing 15 posts - 53,476 through 53,490 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply