Is there a way to generate a list of JOINS being performed without indexes in MySQL?
In SQL Server, I'm looking at TableA, which currently has a uniqueidentifier clustered primary key. The GUID has no meaning in any context.
(I'll give you a second to clean up your keyboard and monitor and set down the soda.)
I'd like to drop that primary key and add a new unique integer primary key to the table. My question is this: when I drop the index, modify the column from uniqueidentifier to int, and add the new clustered unique primary key to the modified column, will the new PK values be in the order of insertion into the table, or will they be in some other order? Is this the right way to go here? Will this work? (I'm kind of a noobkin with regard to table creation/modification.)
I am in charge of a smaller DB 300+ hundred megs 100'ish tables about 45 users hit it throughout the work day. Mostly reads, but a fair number of updates and inserts. I have been slowing learning the structure of the DB in order to get some performance out of it. I have heard that taking a look at indexes is a good place to start. All most all of the indexes for the tables of said DB are clustered, a few of them are non-clustered.
Is there any speed advantage over clustered vs non-clustered? I have a maintenance plan (yes yes, I know) that re-orgs and rebuild the indexes nightly before diff backups, is this good enough for now, until I get a better grip on index formation and utilization?
Is/are there (a) script(s) that will help me view 'performance' of the various indexes? Just how big of can of worms did I get my self into?
I have a large table which has a clustered index with an identity primary key. I'm deciding on the correct value for the fill factor for this table to minimise page splits. We maintain indexes using a script run daily which measures fragmentation and takes appropriate action. The table contains variable length columns.
My first thought was to set it at 100 (as records should only be written to then end of the table) but I assume that changes to the variable length columns could also cause page splits, so I'm now veering towards 90.
Any advice appreciated.
Our nightly full (and periodic differential) backups are becoming quite large, due mostly to the amount of indexes on our tables; roughly half the backup size is comprised of indexes.
We're using the Simple recovery model for our backups.
Is there any way, through using FileGroups or some other file-partitioning method, to exclude indexes from the backups?
It would be nice if this could be extended to full-text catalogs, as well.