One Million Tables

Question

One Million Tables

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734502 More actions · Answer 1

Eric M Russell - Thursday, May 10, 2018 7:10 AM
But can SQL Server support a single table spread across 1 million partitions and files?

No, 15,000 partitions. 32k files 😉

Steve Jones - SSC Editor SSC Guru Points: 734502 More actions · Answer 2

james.bookman - Thursday, May 10, 2018 7:41 AM
You asked about PowerShell: "Note: If I'm testing poorly with PoSh, let me know. I'd be curious why there would be a difference if PoSh is slowed with Get-Random somehow. "
I think your method for measuring elapsed time and the Write-Host cmdlet is having a much larger impact on your performance. I ran 4 tests which I'll attach, but first the summaries.
Run 1 - Removed SQL insert, wrapped original test in Measure-Command statement. TotalSeconds : 4.4825437
Run 2 - Removed SQL insert, removed Get-Date/ New-TimeSpan method for performance measurement. TotalSeconds : 3.6304238
Run 3 - Removed SQL insert, removed Get-Date/ New-TimeSpan method for performance measurement, commented out write-host. TotalSeconds : 0.392731
Run 4 - Removed SQL insert, removed Get-Date/ New-TimeSpan method for performance measurement, commented out write-host, increased loops to 1 million. TotalSeconds : 38.7010968

Perhaps, but the insert is what matters here. We want to be sure that we look at SQL Server causing delays searching for or managing one million tables. Certainly the write-host stuff is slow, so I removed that from tests. Interesting to see your results without the inserts. One million slower, but that's expected if you have more loops.

RichB SSCrazy Eights Points: 9651 More actions · Answer 3

Back in the day people used to talk about making sure you used all the parts of the name to prevent extra lookups on default schemas and so on.

I'd be interested to see the results of changing your script for the inserts to first add the schema and then another run through adding the dbname and schema.

See if that is still a real benefit if it ever was?

james.bookman SSC Rookie Points: 40 More actions · Answer 4

Steve Jones - SSC Editor - Thursday, May 10, 2018 4:24 PM
james.bookman - Thursday, May 10, 2018 7:41 AM
You asked about PowerShell: "Note: If I'm testing poorly with PoSh, let me know. I'd be curious why there would be a difference if PoSh is slowed with Get-Random somehow. "
I think your method for measuring elapsed time and the Write-Host cmdlet is having a much larger impact on your performance. I ran 4 tests which I'll attach, but first the summaries.
Run 1 - Removed SQL insert, wrapped original test in Measure-Command statement. TotalSeconds : 4.4825437
Run 2 - Removed SQL insert, removed Get-Date/ New-TimeSpan method for performance measurement. TotalSeconds : 3.6304238
Run 3 - Removed SQL insert, removed Get-Date/ New-TimeSpan method for performance measurement, commented out write-host. TotalSeconds : 0.392731
Run 4 - Removed SQL insert, removed Get-Date/ New-TimeSpan method for performance measurement, commented out write-host, increased loops to 1 million. TotalSeconds : 38.7010968
Perhaps, but the insert is what matters here. We want to be sure that we look at SQL Server causing delays searching for or managing one million tables. Certainly the write-host stuff is slow, so I removed that from tests. Interesting to see your results without the inserts. One million slower, but that's expected if you have more loops.

Yes, the insert is the most import thing and I'll leave making that faster to the real SQL admins. At best, I'm a former causal SQL admin. I am a Microsoft Exchange admin who spends his days knee deep in PowerShell.
Specifically you were wondering about the Get-Random cmdlet.
I ran test 4 again this morning and increased the Maximum parameter from 1,000 to 1,000,000. The first run yielded 30.3 seconds and the second run yielded 30.9 seconds.
So comparing those tests to yesterday's "Run 3", the answer to your question about Get-Random slowing down your PowerShell SQL script is no.

Out of curiosity I added the write-host cmdlet back to today's run (1 million loops and Get-Random -Maximum 1000000) and got a result of 5 minutes 31 seconds or 331.5 seconds.

Steve Jones - SSC Editor SSC Guru Points: 734502 More actions · Answer 5

I got rid of those databases, but I'll see if I can go back and add schema information.That's a good point, though I wonder if this isn't just poor indexing on the part of the system tables.

Eric M Russell SSC Guru Points: 125523 More actions · Answer 6

... Someone had tried to simulate a large federated load of data by creating a million tables in a MySQL systemcreating a million tables in a MySQL system...

When I think of a "federated" database system, at least in the context of a non-MPP RDMS like SQL Server, it distributes the tables across multiple servers and leverages linked servers and views containing unionized selects.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

INCREDIBLEmouse SSC Eights! Points: 835 More actions · Answer 7

I'm more curious about SSMS Object Exploder: Did you try to expand the list? Did it ever work? :hehe:

Steve Jones - SSC Editor SSC Guru Points: 734502 More actions · Answer 8

INCREDIBLEmouse - Friday, May 11, 2018 12:43 PM
I'm more curious about SSMS Object Exploder: Did you try to expand the list? Did it ever work? :hehe:

No, exploded.

I did have this fail once while building tables, and it seemed that OE displayed around 200k servers.