@MaxVernon Using WAITFOR DELAY doesn't always help if the query I want to analyze completes before the WAITFOR starts, and it always has to. It makes testing Triggers, Transactions, this stuff, etc much harder. BUT, a very simple function that does a Thread.Sleep(MillisecondsToWait.Value); will run once per row of th result set and hence the current query won't finish until I say it can finish!! But that leaves the execution context active long enough to run some tests :-D
@dezso Sorry about that, but I still don't agree it should be removed.
I disagree, on balance. The question is already closed, so no new answers can be posted. Anyone finding this Q & A via search will at least find some useful pointers from this answer. It is clearly too long for a comment. Overall, I feel it is better we keep this; deleting it would not improve the site. — Paul White ♦2 hours ago
Database Administrators' fourth moderator election has come to a close, the votes have been tallied, and the new moderator is:
He'll be joining the existing crew shortly — please thank him for volunteering, and share your assistance and advice as he learns the ropes!
For details on how the v...
I'm preparing to delete this as it is considered incorrect
Subject Name is also a non-prime attribute and it depends on part of the primary key Subject Code (breaks rule - there must not be any partial dependency of any column on primary key).
This is prohibited in 2nd Normal Form and should th...
Situation is, customer says it takes too long so you have to build this and that in your application. I want to know if the times they say is too long is reasonable or if the guys managing SQL Server on the customer end is no good at what they are doing.
Half of the data is documents in a varbinary. The request is to store those outside of DB to make maintenance easier. I'm not a big fan of that in this application so looking for other solutions to the problem.
Except for the rare occasion when the medical records for a patient should be destroyed for ever and ever. Then there is deletion but that can be handled as deleting references in other tables and do some cleanup job later.
@PaulWhite For part of the functionality the blobs need to be available from TSQL and that can be technically challenging. But most of all it is the the uncertainty on my part of leaving it up to a customer to implement a filestorage thing that does the job right and don't spread these document to places where they don't belong. Or even accidentally deleting or something like that. Just feels good to have those updates in a transaction with the rest of the medical records.
A mixup (wrong document for the wrong person) in this could mean life or death for a patient.
There is also a quality assurance thing that we guarantee. The product is CE verified.
The CE mark, or formerly EC mark, is a mandatory conformity marking for certain products sold within the European Economic Area (EEA) since 1985. The CE marking is also found on products sold outside the EEA that are manufactured in, or designed to be sold in, the EEA. This makes the CE marking recognizable worldwide even to people who are not familiar with the European Economic Area. It is in that sense similar to the FCC Declaration of Conformity used on certain electronic devices sold in the United States.
It consists of the CE logo and, if applicable, the four digit identification number of...
I have created 3 jobs in my MS SQL Server
The job returns an error on 2. Remove top 4 lines
Message
Executed as user: myDomain\administrator. The process could not be created for step 2 of job 0xA849BBB21D41B64387336C2CAA7FB632 (reason: The system cannot find the file specified). The st...
At the moment this does not seem like a possible value to get, which is unfortunate as this does seem like a handy value to have.
Ok, I did find it (mostly). There is a last_execution_time field in the sys.dm_exec_query_stats DMV, though that DMV comes with the following warning:
An initial ...
@srutzky This is an admirable effort, but as you note, it would fail for multiple concurrent executions of the same cached plan. Really there's no DMV support for this, we'd need to use Extended Events or Profiler. The requests DMV, for example, is primarily for MARS support. A request is not a statement in a batch, as many people expect.
There's a good reason sp_WhoIsActive does not show elapsed time for the current statement. There's just no safe, computationally-lightweight way to do it.
@PaulWhite "as you note, it would fail for multiple concurrent executions of the same cached plan. " ... "requests DMV, for example, is primarily for MARS support.": tru dat, BUT, on a pragmatic level, how often is this information mostly, or entirely, accurate vs inaccurate due to concurrency and/or MARS? How many people use MARS outside of the Lazy Loading feature in Entity Framework?
@PaulWhite oh, I so badly want to answer that question with a pragmatic explanation of what a constraint is, and why you couldn't possibly want to remove the column underlying the constraint, whilst leaving the constraint in place.
@PaulWhite I would suspect that well over 50% of the time this info is "good enough" to get a sense of things. And for the circumstances where it is less accurate, that could be acceptable as long as expectations are set accordingly.
Given that this particular info is mostly needed when something is taking a rather long time to complete, how often would it be the case where that exact same query is also running much faster at the same time in other sessions? Yes, it certainly can happen. But, does it happen often enough to entirely invalidate this info?
i've not heard any morph of pragma used in the last 12 months, and now i see it twice in consecutive chat messages of the heap. another reason to love it.
@srutzky Let's leave the MARS thing for a second - I only mention that because people in general often expect requests to change for each statement in the batch. How often the information would be wrong depends on how often the same plan is concurrently executed. Given that SQL Server is designed to reuse plans and facilitate concurrent execution, the chances seem quite high.
@MaxVernon That's part of the reason I put it on hold ;)
@srutzky I can't speak to what proportion of the time it would 'work', but I do know I like to be able to trust my monitoring data. Never knowing if it were correct or not would be ... concerning.
@srutzky It's not just that it might be running faster; it might overlap at all. And you could never know. I'm not saying it's a hopeless idea at all. It is an "admirable effort" given the lack of proper DMV support.
But really, what we need is a DMV that shows per-statement metrics.
@PaulWhite I certainly understand the issue of "SQL Server is designed to reuse plans and facilitate concurrent execution", which is why this data is not 100% reliable. But, I am just trying to put things into perspective, balancing what is possible vs what is most likely to actually happen.
I am just saying that as a diagnostic tool, if it was understood that the info is not guaranateed (which seems acceptable given that MS has that warning on the MSDN page for query_stats), then is it better to have something that is more often correct than incorrect, or not available at all because it might sometimes be incorrect?
@srutzky For mine, no. I think we differ in how often the information is likely to be wrong. SQL Servers reuse and concurrently execute plans all the time. I just can't imagine using q_stats would be useful in anything except a single-user test environment. You should see some of the edge cases we turned up while developing the initial release of (open-source) sp_WhoIsActive for example.
@swasheck Not as I understand it currently. It's more about plan changes and performance differences for the whole batch.
@srutzky Regarding the accuracy of query_stats, that warning is mostly about being up-to-date and consistent with other DMVs. There's a difference between that and simply wrong information.
I don't want to come across as too negative on the idea though, perhaps it sounds that way. Apologies if so.
@swasheck One never knows what might be in the pipeline though.
The whole instrumentation/DMV/XE split is odd though. It forces tools like Plan Explorer to run XE sessions in the background, retrieve some information from DMVs, and some via SET statements, for example.
Really, the idea of starting and stopping a Profiler Trace or XE session at the same time as the DMV-accessing query is not much different.
That seems like a cleaner and more reliable solution to the question that using query_stats.
@PaulWhite All of that is understood. And no, you are not coming across as negative, at least not to me. Absolutely nothing wrong with sharing knowledge / perspective / experience / opinion.
Introducing U-SQL – A Language that makes Big Data Processing Easy - The Visual Studio Blog - Site Home - MSDN Blogs http://blogs.msdn.com/b/visualstudio/archive/2015/09/28/introducing-u-sql.aspx
@MaxVernon so was hp's ... a laptop with a twisty foldy touch screen. anyway. keeping up with folks' "innovation" is exhausting. it'd sure be nice if we could reduce clutter and use all of this technology to do some good for humanity.
@PaulWhite Interesting. So first they bring SQL / set-based semantics into .NET, which gave us LINQ (and then Entity Framework), and now they are bringing LINQ (set-based .NET) into SQL Server (Azure SQL Database, whatever). This is very incestuous ;-). But lessons-learned do make for better products :).
@PaulWhite "Who won? The DBAs or the Developers?" In reality? The not-so-many of us who understand both SQL and C#. It just thins the herd a bit, creating a higher barrier to entry in the field. But I suppose that is progress. I have an uncle who works on cars and he was telling me how things have changed over the years, where it used to be that anyone at least had a chance of fixing a car, but now it is mostly computerized / electronic and requires quite a bit more education.
@PaulWhite So DBA's have more ability to do wonky things, and developers have even more ability to write procedural / non-set-based SQL than they do now ;-)
And both sides now have less reason to talk to each other ;)
This is one of the main down-sides to SQLCLR: it allows for someone who is really good at writing good code in one paradigm to use that skill to write horribly bad code in another paradigm that has different rules (rules that they -- often enough -- don't have time and/or desire to learn).
Satya Narayana Nadella (/ˈnədɛlæ/; born August 19, 1967) is an Indian-American business executive. He is the current chief executive officer (CEO) of Microsoft. He was appointed as CEO on 4 February 2014, succeeding Steve Ballmer. Before becoming CEO of Microsoft, he was Executive Vice President of Microsoft's Cloud and Enterprise group, responsible for building and running the company's Computing Platforms, Developer Tools and Cloud Computing Services.
== Early lifeEdit ==
Nadella was born into a Telugu speaking family in Hyderabad, Andhra Pradesh (now Telangana), India. His father, Bukkapuram...
Since you won't be at the Summit, I think enjoyment is a forgone conclusion
Fair enough
I disagree, on balance. The question is already closed, so no new answers can be posted. Anyone finding this Q & A via search will at least find some useful pointers from this answer. It is clearly too long for a comment. Overall, I feel it is better we keep this; deleting it would not improve the site. — Paul White ♦13 hours ago
@PaulWhite A drive connection standard that more or less sits directly on the PCIe bus without an intervening SATA layer. It can process some ungodly number of parallel requests and is very shiny and fast indeed.
There is also a version that uses the same physical connector as SATA
I find myself in a situation where I have inherited the db and people complain about database performance. I identified about a dozend of tables not having a clustered index or a not carefully planned clustered index, some of them with lots of data and frequently used. I am actually struggeling t...
Note that this version has big limitations as it "provides the ability to create and edit Reporting Services projects that are reporting on LOCAL relational data in local SQL Server EXPRESS databases. — SomeGuyJun 8 '12 at 14:14
@PaulWhite We are licensed. We have 2005 SSRS x64. One of our user has an x86 machine and needs 2005 BIDS x86. I believe its because the initial RDL files were made in 2005, using any different visual studio would cause them to be upgraded and not backwards capable, right?
I know its a strange question, for sure. We are upgrading the SSRS servers to 2014 in a few months (we finally brought our main prod box to 2014) so this is just a band aid for the time being. I may just need to install it on a sandbox that is x64
@MikeFal To not muddy the waters with the other devs who need those reports in bids 2005
Like i said, its jsut a bandaid for a few months until we upgrade to SSRS 2014
Oh, the RDLs should still work on 2005 even with the more current version of the tools, I believe. I think only the project files get upgraded so you couldn't open them in BIDs. Everyone else would have to upgrade.
While we don't do licensing questions... if you install standard edition on server A, report developer on machine B wants to have BIDS so they can develop reports. I assume installing BIDS (which only exists on server disk) on their machine would be a license violation
I wouldn't think so, otherwise people would have to license BIDS for each and every machine that its developers use. I know MS is a money-hungry, greedy machine but thats alot even for them
Wouldnt that mean that SSDT would also ened to be licensed, per machine?
Is this a good enough example of the queries returning different results?: SQLfiddle. Unless there is some FK relationship between the 2 tables, the queries are not equivalent. — ypercube12 mins ago
I forgot to add "if Hibernate is producing this 2nd query, fire him"