@Zane There are some people I work with who get really excited about his techniques for manipulating row goals and forcing parallelism and forget to look at basic things like normalization, indexing, and simplifying queries first.
@ypercube both of my questions have been very visible things that have happened on SO in the past several months which is probably why they've been accepted.
Your question is really good, just makes me feel a bit bad if it seemed that I overstepped when I became a mod.
@ypercube funny, this is a draft that I wrote before leaving work yesterday:
> Let's assume that before being elected a mod you used to frequent a SE chatroom. Users there are very knowledgeable (sometimes), but some have a tendency to post some snarky comments (which points to real problems with the question/answer)......
Disclaimer: I'm assuming that the candidates are gonna answer truthfully
Time to truge through the old index design and optimization book. I want my key column to address the JOIN columns first, right? Then the WHERE columns, then includes for anything returning that isn't being used to filter/join?
This is something that has bugged me for many many years and have never asked.
Why is it that when writing SQL against a schema in a databases, whether it be Oracle, Postgres, MySQL or MSSQL I have to specify the database name and the table name? e.g.
SELECT id FROM mydb.mytable;
and other times...
Right now i do. My client can't be bothered to build the tables i need. So I'm doing some QA and then reviewing Summit abstracts and working on my presentation for Friday.
@swasheck Also, that was not a "you are late to the party" statement, that was meant to say "this is funny and everyone should watch it". Emphasis was on the lol, not the yesterday.
@mmarie no worries. i didnt really care if it was a "you are late to the party" statement because ... well ... i've spent the last week pulling cognos' head out of its own ass
somehow it leaked that a dev created an index in production and that has been touted as the root cause because that changed all of the query plans from then until the end of eternity.
@PaulWhite ... and i'm still an idiot ... a slow learner who'd need your intelligent guidance on the histogram comparison thing. if you ever wanted to do a blog post with pretty pictures on how the histos are aligned for joins then that'd be awesome because i still dont understand which values it'd use to set as boundary points. yes. i'm an idiot.
@swasheck 1. Draw two small histograms next to each other. 2. Where a boundary touches a non-boundary, extend it so it splits the non-aligned boundary in two. 3. Do the obvious math. 4. Profit.
@ypercube "Sam, a brilliant child, leaves home for the mountains after being told that the family summer trip has been canceled, thus preventing him from doing the algae experiments he had planned for that summer."
It can be contention on the underlying system structures exposed by the DMV. Not much you can do about latching but this is a case where I would typically use a WITH (READUNCOMMITTED) hint.
The counter value Aaron used in his filter does not exist, so the CASE was churning on calculating, the "@total_buffer" was NULL: db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer)
@ShawnMelton That's the same link I posted above. And you're reading the table wrong. That's for SQLServer:Buffer Node, scroll up a bit to see Total Pages for SQLServer:Buffer Manager. I would still use the SUM OVER instead.
WITH src AS
(
SELECT
database_id,
db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors WITH (READUNCOMMITTED)
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] =
CASE [database_id]
WHEN 32767 THEN N'Resource DB'
ELSE DB_NAME([database_id])
END,
src.db_buffer_pages,
db_buffer_MB = src.db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
src.db_buffer_pages * 100.0 /
Funny part of the story...finding the database memory usage I send an email to the client and they immediately come back asking if I am on the right server. I stated it had only 8GB, they said that server has 32GB....screenshot I sent says otherwise though :)
OH: "i don't want anyone to be fired ... i'm just saying that we want you to understand that when we say that devs shouldn't have sa access in production environments we're not just saying it to be dickheads."
@MichaelGreen I'd love to know if that's possible, too. I think it's fringe as far as 'on-topic' but it's definitely something that seems to have interest.
Unfortunately for me I deal with 2005, 2008R2 and 2014
@AaronBertrand are you available? I need to debunk something and I know I've heard to talk many times about situations where without and ORDER BY there is not guarantee of order.
A coworker is arguing that if he does ORDER BY when inserting into a table it will always come out that way. I'm trying to explain that that is indeed not the case.
Coworker is wrong. What he is seeing is coincidental and not guaranteed at all. He's already spent more time arguing about it than he would have spent typing out the damned ORDER BY clause.
I don't know why people want to argue about shit that happens to work right in a certain scenario when they can guarantee it in all scenarios with a few keystrokes.
Feel free to screen shot this chat and give it to your co-worker. I'll gladly prove him wrong directly.
I'm about to get on a plane, so maybe not immediately.
@Shawn Yeah those were written before 2012 came out, I don't know why I used Total Pages there. That would be based on the underlying OS, though, not version of SQL Server.
I was wondering if there is a way to order by consecutive numbers in Sql Server 2008.
Currently I have
"Select StoreNum, StoreName, Date, Time From tbl_stores ORDER BY StoreNum, Date"
Which will give me
1 | Toronto Store | 2015-03-04 | ... |
1 | Toronto Store | 2015-03-05 | ... |
2 | ...
> The problem only occurs with partitioned tables with a GROUP BY or DISTINCT over three or more columns using an aligned partitioned index, where a single partition is processed.
I'm just looking at the SEDE sys tables here trying to find if there is a table with composite keys. I just don't really understand all this back-end stuff, I normally mostly deal with front-end DDL
@Lamak I thought that's where you were going with it but I thought about going through a large result set looking for times when the table name appeared twice and my eyes started to hurt bleed. Figured I'd toss in my .02. :)
As per this blog , the key difference between DBMS & RDBMS states:
Key Difference between DBMS and RDBMS So what is the main difference
between DBMS and RDBMS? The key difference is that RDBMS (relational
database management system) applications store data in a tabular form,
while DBMS...
@sᴉɔuɐɹɥԀ Just out of interest, what is it exactly you're looking for? All indexes with more than one key? Just unique compound indexes? Just unique compound primary keys (clustered or nonclustered)? Also: why? Genuinely interested; not being confrontational.
I try to rename a database or a table in SSMS 2014 (with CU6 applied), by clicking and selecting the DB or table in the Object Explorer and then pressing F2 to edit the name. The Delete key on my keyboard will not delete the character to the right of the cursor.
Is this just a problem on my end ...
> A real developer could fix this problem in a few minutes. For Microsoft, 4 years and it's still broken. Microsoft needs a new SQL Server Manageability team leader to help get everyday simple issues like this resolved, issues that thousands have to get annoyed with daily.
SELECT
AVG(Cont.Equipment_Retail)
FROM STAR_Contracts AS Cont
INNER JOIN STAR_Products AS Prod
ON Cont.Contract_Number = Prod.Contract_Number
AND COUNT(Prod.Contract_Number) = 1
WHERE Prod.Product_ID = '1018'
AND Prod.Sequence_Number = '001'
GROUP BY Cont.Equipment_Retail;
> An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
select i.name, count(p.index_id)
from sys.indexes i
join sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
and count(p.partition_number) = 1
group by i.name
Msg 1015, Level 15, State 1, Line 18
An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.
@sᴉɔuɐɹɥԀ The problem with that is the optimizer will produce a reasonable plan based on the indexes and data distribution of the SEDE data set. Chances are, the same SQL syntax with your table names substituted will generate a completely different execution plan, because the optimizer's search path and cost estimates will all be different.
@sᴉɔuɐɹɥԀ Rewriting a query using different syntax (while keeping the semantics exactly the same) is a valid tuning technique, but it's close to last resort. Write the query naturally, against a well-indexed relational database, with representative metadata e.g. statistics - chances are high the optimizer will find a good plan. Until you have that baseline, you have no basis for analysis or rewriting the query (as a last resort) anyway.
Also, your backwards name makes pinging you manually a bit awkward.
@sᴉɔuɐɹɥԀ In summary, I think you're doing things backwards. Could be fun and educational, but that's the main value you're going to get out of the SEDE exercise. Be careful not to learn stuff that's wrong, though :)
@Lamak Yes I know. I wanted to say it too, using different words.
I was wondering if there is a way to order by consecutive numbers in Sql Server 2008.
Currently I have
"Select StoreNum, StoreName, Date, Time From tbl_stores ORDER BY StoreNum, Date"
Which will give me
1 | Toronto Store | 2015-03-04 | ... |
1 | Toronto Store | 2015-03-05 | ... |
2 | ...
As per this blog , the key difference between DBMS & RDBMS states:
Key Difference between DBMS and RDBMS So what is the main difference
between DBMS and RDBMS? The key difference is that RDBMS (relational
database management system) applications store data in a tabular form,
while DBMS...
What's the difference between rows and log type_desc in sys.master_files ?
