[Pdns-users] Latency with MSSQL backend

[Adam Breaux]

Any way to trim the latency using PDNS with a MSSQL backend?

I've got a dual 1Ghz processor box running MSSQL 2K Ent with PowerDNS
and the lowest latency score I can get is 8 msec. With my BIND servers I
get as low as 0 msec.

Is there any PowerDNS-side tuning I can do? I have the following schema
loaded:

/****** TABLE: supermasters ******/
CREATE TABLE [dbo].[supermasters] (
	[ip] varchar(25) NOT NULL,
	[nameserver] varchar(255) NOT NULL, 
	[account] varchar(40) DEFAULT NULL
)

/****** TABLE: history_supermasters ******/
CREATE TABLE [dbo].[history_supermasters] (
	[ip] varchar(25) NOT NULL,
	[nameserver] varchar(255) NOT NULL, 
	[account] varchar(40) DEFAULT NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[trigger_supermasters]
ON [dbo].[supermasters]
FOR UPDATE AS
INSERT INTO history_supermasters(
	ip,
	nameserver,
	account)
SELECT * FROM deleted
GO

/****** TABLE: domains ******/
CREATE TABLE [dbo].[domains] (
	[id] [int] IDENTITY(1, 1) NOT NULL,
	[name] varchar(255) NOT NULL,
	[master] varchar(20) DEFAULT NULL,
	[last_check] [int] DEFAULT NULL,
	[type] varchar(6) NOT NULL DEFAULT 'NATIVE',
	[notified_serial] [int] DEFAULT NULL, 
	[account] varchar(40) DEFAULT NULL,
	PRIMARY KEY (id),
	UNIQUE (name)
) ON [PRIMARY]
GO

/****** TABLE: history_domains ******/
CREATE TABLE [dbo].[history_domains] (
	[id] [int] NOT NULL,
	[name] varchar(255) NOT NULL,
	[master] varchar(20) DEFAULT NULL,
	[last_check] [int] DEFAULT NULL,
	[type] varchar(6) NOT NULL DEFAULT 'NATIVE',
	[notified_serial] [int] DEFAULT NULL, 
	[account] varchar(40) DEFAULT NULL,
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[trigger_domains]
ON [dbo].[domains]
FOR UPDATE AS
INSERT INTO history_domains(
	id,
	name,
	master,
	last_check,
	type,
	notified_serial,
	account)
SELECT * FROM deleted
GO

/****** TABLE: types ******/
CREATE TABLE [dbo].[types] (
	[type] varchar(6) NOT NULL,
	PRIMARY KEY (type)
) ON [PRIMARY]
GO

/****** INSERT: defaults ******/
INSERT INTO [dbo].[types] (type) VALUES ('A')
GO
INSERT INTO [dbo].[types] (type) VALUES ('CNAME')
GO
INSERT INTO [dbo].[types] (type) VALUES ('MX')
GO
INSERT INTO [dbo].[types] (type) VALUES ('NS')
GO
INSERT INTO [dbo].[types] (type) VALUES ('PTR')
GO
INSERT INTO [dbo].[types] (type) VALUES ('SOA')
GO

/****** TABLE: records ******/
CREATE TABLE [dbo].[records] (
	[id] [int] IDENTITY(1, 1) NOT NULL,
	[domain_id] [int] NOT NULL
		FOREIGN KEY (domain_id) REFERENCES domains(id),
	[name] varchar(255) DEFAULT NULL,
	[type] varchar(6) NOT NULL
		FOREIGN KEY (type) REFERENCES types(type),
	[content] varchar(255) DEFAULT NULL,
	[ttl] [int] DEFAULT NULL,
	[prio] [int] DEFAULT NULL,
	[change_date] [int] DEFAULT NULL,
	PRIMARY KEY (id)
)

/****** INDEX: complex index on primary fields ******/
CREATE INDEX [rec_name_index] 
	ON [dbo].[records] (name)
	WITH FILLFACTOR = 80
	ON [PRIMARY]
GO

/****** INDEX: complex index on primary fields ******/
CREATE INDEX [name_type_index] 
	ON [dbo].[records] (name,type)
	WITH FILLFACTOR = 80
	ON [PRIMARY]
GO

/****** INDEX: complex index on primary fields ******/
CREATE INDEX [domain_id_index] 
	ON [dbo].[records] (domain_id)
	WITH FILLFACTOR = 80
	ON [PRIMARY]
GO

/****** TABLE: history_records ******/
CREATE TABLE [dbo].[history_records] (
	[id] [int] NOT NULL,
	[domain_id] [int] NOT NULL,
	[name] varchar(255) DEFAULT NULL,
	[type] varchar(6) NOT NULL,
	[content] varchar(255) DEFAULT NULL,
	[ttl] [int] DEFAULT NULL,
	[prio] [int] DEFAULT NULL,
	[change_date] [int] DEFAULT NULL,
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[trigger_records]
ON [dbo].[records]
FOR UPDATE AS
INSERT INTO history_records(
	id,
	domain_id,
	name,
	type,
	content,
	ttl,
	prio,
	change_date)
SELECT * FROM deleted
GO

/****** VIEW: join key tables ******/
CREATE VIEW [dbo].[view_zones] AS
	SELECT a.name, b.name AS hostname, b.content, c.type, b.ttl,
b.prio
	FROM dbo.domains a 
	INNER JOIN dbo.records b ON a.id = b.domain_id 
	INNER JOIN dbo.types c ON b.type = c.type
GO